1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23from functools import reduce
  24
  25from sqlglot._typing import E
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    seq_get,
  33    subclasses,
  34)
  35from sqlglot.tokens import Token
  36
  37if t.TYPE_CHECKING:
  38    from sqlglot.dialects.dialect import DialectType
  39
  40
  41class _Expression(type):
  42    def __new__(cls, clsname, bases, attrs):
  43        klass = super().__new__(cls, clsname, bases, attrs)
  44
  45        # When an Expression class is created, its key is automatically set to be
  46        # the lowercase version of the class' name.
  47        klass.key = clsname.lower()
  48
  49        # This is so that docstrings are not inherited in pdoc
  50        klass.__doc__ = klass.__doc__ or ""
  51
  52        return klass
  53
  54
  55SQLGLOT_META = "sqlglot.meta"
  56
  57
  58class Expression(metaclass=_Expression):
  59    """
  60    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  61    context, such as its child expressions, their names (arg keys), and whether a given child expression
  62    is optional or not.
  63
  64    Attributes:
  65        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  66            and representing expressions as strings.
  67        arg_types: determines what arguments (child nodes) are supported by an expression. It
  68            maps arg keys to booleans that indicate whether the corresponding args are optional.
  69        parent: a reference to the parent expression (or None, in case of root expressions).
  70        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  71            uses to refer to it.
  72        comments: a list of comments that are associated with a given expression. This is used in
  73            order to preserve comments when transpiling SQL code.
  74        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  75            optimizer, in order to enable some transformations that require type information.
  76        meta: a dictionary that can be used to store useful metadata for a given expression.
  77
  78    Example:
  79        >>> class Foo(Expression):
  80        ...     arg_types = {"this": True, "expression": False}
  81
  82        The above definition informs us that Foo is an Expression that requires an argument called
  83        "this" and may also optionally receive an argument called "expression".
  84
  85    Args:
  86        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  87    """
  88
  89    key = "expression"
  90    arg_types = {"this": True}
  91    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  92
  93    def __init__(self, **args: t.Any):
  94        self.args: t.Dict[str, t.Any] = args
  95        self.parent: t.Optional[Expression] = None
  96        self.arg_key: t.Optional[str] = None
  97        self.comments: t.Optional[t.List[str]] = None
  98        self._type: t.Optional[DataType] = None
  99        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 100        self._hash: t.Optional[int] = None
 101
 102        for arg_key, value in self.args.items():
 103            self._set_parent(arg_key, value)
 104
 105    def __eq__(self, other) -> bool:
 106        return type(self) is type(other) and hash(self) == hash(other)
 107
 108    @property
 109    def hashable_args(self) -> t.Any:
 110        return frozenset(
 111            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 112            for k, v in self.args.items()
 113            if not (v is None or v is False or (type(v) is list and not v))
 114        )
 115
 116    def __hash__(self) -> int:
 117        if self._hash is not None:
 118            return self._hash
 119
 120        return hash((self.__class__, self.hashable_args))
 121
 122    @property
 123    def this(self) -> t.Any:
 124        """
 125        Retrieves the argument with key "this".
 126        """
 127        return self.args.get("this")
 128
 129    @property
 130    def expression(self) -> t.Any:
 131        """
 132        Retrieves the argument with key "expression".
 133        """
 134        return self.args.get("expression")
 135
 136    @property
 137    def expressions(self) -> t.List[t.Any]:
 138        """
 139        Retrieves the argument with key "expressions".
 140        """
 141        return self.args.get("expressions") or []
 142
 143    def text(self, key) -> str:
 144        """
 145        Returns a textual representation of the argument corresponding to "key". This can only be used
 146        for args that are strings or leaf Expression instances, such as identifiers and literals.
 147        """
 148        field = self.args.get(key)
 149        if isinstance(field, str):
 150            return field
 151        if isinstance(field, (Identifier, Literal, Var)):
 152            return field.this
 153        if isinstance(field, (Star, Null)):
 154            return field.name
 155        return ""
 156
 157    @property
 158    def is_string(self) -> bool:
 159        """
 160        Checks whether a Literal expression is a string.
 161        """
 162        return isinstance(self, Literal) and self.args["is_string"]
 163
 164    @property
 165    def is_number(self) -> bool:
 166        """
 167        Checks whether a Literal expression is a number.
 168        """
 169        return isinstance(self, Literal) and not self.args["is_string"]
 170
 171    @property
 172    def is_int(self) -> bool:
 173        """
 174        Checks whether a Literal expression is an integer.
 175        """
 176        if self.is_number:
 177            try:
 178                int(self.name)
 179                return True
 180            except ValueError:
 181                pass
 182        return False
 183
 184    @property
 185    def is_star(self) -> bool:
 186        """Checks whether an expression is a star."""
 187        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 188
 189    @property
 190    def alias(self) -> str:
 191        """
 192        Returns the alias of the expression, or an empty string if it's not aliased.
 193        """
 194        if isinstance(self.args.get("alias"), TableAlias):
 195            return self.args["alias"].name
 196        return self.text("alias")
 197
 198    @property
 199    def alias_column_names(self) -> t.List[str]:
 200        table_alias = self.args.get("alias")
 201        if not table_alias:
 202            return []
 203        return [c.name for c in table_alias.args.get("columns") or []]
 204
 205    @property
 206    def name(self) -> str:
 207        return self.text("this")
 208
 209    @property
 210    def alias_or_name(self) -> str:
 211        return self.alias or self.name
 212
 213    @property
 214    def output_name(self) -> str:
 215        """
 216        Name of the output column if this expression is a selection.
 217
 218        If the Expression has no output name, an empty string is returned.
 219
 220        Example:
 221            >>> from sqlglot import parse_one
 222            >>> parse_one("SELECT a").expressions[0].output_name
 223            'a'
 224            >>> parse_one("SELECT b AS c").expressions[0].output_name
 225            'c'
 226            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 227            ''
 228        """
 229        return ""
 230
 231    @property
 232    def type(self) -> t.Optional[DataType]:
 233        return self._type
 234
 235    @type.setter
 236    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 237        if dtype and not isinstance(dtype, DataType):
 238            dtype = DataType.build(dtype)
 239        self._type = dtype  # type: ignore
 240
 241    def is_type(self, *dtypes) -> bool:
 242        return self.type is not None and self.type.is_type(*dtypes)
 243
 244    @property
 245    def meta(self) -> t.Dict[str, t.Any]:
 246        if self._meta is None:
 247            self._meta = {}
 248        return self._meta
 249
 250    def __deepcopy__(self, memo):
 251        copy = self.__class__(**deepcopy(self.args))
 252        if self.comments is not None:
 253            copy.comments = deepcopy(self.comments)
 254
 255        if self._type is not None:
 256            copy._type = self._type.copy()
 257
 258        if self._meta is not None:
 259            copy._meta = deepcopy(self._meta)
 260
 261        return copy
 262
 263    def copy(self):
 264        """
 265        Returns a deep copy of the expression.
 266        """
 267        new = deepcopy(self)
 268        new.parent = self.parent
 269        return new
 270
 271    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 272        if self.comments is None:
 273            self.comments = []
 274        if comments:
 275            for comment in comments:
 276                _, *meta = comment.split(SQLGLOT_META)
 277                if meta:
 278                    for kv in "".join(meta).split(","):
 279                        k, *v = kv.split("=")
 280                        value = v[0].strip() if v else True
 281                        self.meta[k.strip()] = value
 282                self.comments.append(comment)
 283
 284    def append(self, arg_key: str, value: t.Any) -> None:
 285        """
 286        Appends value to arg_key if it's a list or sets it as a new list.
 287
 288        Args:
 289            arg_key (str): name of the list expression arg
 290            value (Any): value to append to the list
 291        """
 292        if not isinstance(self.args.get(arg_key), list):
 293            self.args[arg_key] = []
 294        self.args[arg_key].append(value)
 295        self._set_parent(arg_key, value)
 296
 297    def set(self, arg_key: str, value: t.Any) -> None:
 298        """
 299        Sets arg_key to value.
 300
 301        Args:
 302            arg_key: name of the expression arg.
 303            value: value to set the arg to.
 304        """
 305        if value is None:
 306            self.args.pop(arg_key, None)
 307            return
 308
 309        self.args[arg_key] = value
 310        self._set_parent(arg_key, value)
 311
 312    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 313        if hasattr(value, "parent"):
 314            value.parent = self
 315            value.arg_key = arg_key
 316        elif type(value) is list:
 317            for v in value:
 318                if hasattr(v, "parent"):
 319                    v.parent = self
 320                    v.arg_key = arg_key
 321
 322    @property
 323    def depth(self) -> int:
 324        """
 325        Returns the depth of this tree.
 326        """
 327        if self.parent:
 328            return self.parent.depth + 1
 329        return 0
 330
 331    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 332        """Yields the key and expression for all arguments, exploding list args."""
 333        for k, vs in self.args.items():
 334            if type(vs) is list:
 335                for v in vs:
 336                    if hasattr(v, "parent"):
 337                        yield k, v
 338            else:
 339                if hasattr(vs, "parent"):
 340                    yield k, vs
 341
 342    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 343        """
 344        Returns the first node in this tree which matches at least one of
 345        the specified types.
 346
 347        Args:
 348            expression_types: the expression type(s) to match.
 349            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 350
 351        Returns:
 352            The node which matches the criteria or None if no such node was found.
 353        """
 354        return next(self.find_all(*expression_types, bfs=bfs), None)
 355
 356    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 357        """
 358        Returns a generator object which visits all nodes in this tree and only
 359        yields those that match at least one of the specified expression types.
 360
 361        Args:
 362            expression_types: the expression type(s) to match.
 363            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 364
 365        Returns:
 366            The generator object.
 367        """
 368        for expression, *_ in self.walk(bfs=bfs):
 369            if isinstance(expression, expression_types):
 370                yield expression
 371
 372    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 373        """
 374        Returns a nearest parent matching expression_types.
 375
 376        Args:
 377            expression_types: the expression type(s) to match.
 378
 379        Returns:
 380            The parent node.
 381        """
 382        ancestor = self.parent
 383        while ancestor and not isinstance(ancestor, expression_types):
 384            ancestor = ancestor.parent
 385        return t.cast(E, ancestor)
 386
 387    @property
 388    def parent_select(self) -> t.Optional[Select]:
 389        """
 390        Returns the parent select statement.
 391        """
 392        return self.find_ancestor(Select)
 393
 394    @property
 395    def same_parent(self) -> bool:
 396        """Returns if the parent is the same class as itself."""
 397        return type(self.parent) is self.__class__
 398
 399    def root(self) -> Expression:
 400        """
 401        Returns the root expression of this tree.
 402        """
 403        expression = self
 404        while expression.parent:
 405            expression = expression.parent
 406        return expression
 407
 408    def walk(self, bfs=True, prune=None):
 409        """
 410        Returns a generator object which visits all nodes in this tree.
 411
 412        Args:
 413            bfs (bool): if set to True the BFS traversal order will be applied,
 414                otherwise the DFS traversal will be used instead.
 415            prune ((node, parent, arg_key) -> bool): callable that returns True if
 416                the generator should stop traversing this branch of the tree.
 417
 418        Returns:
 419            the generator object.
 420        """
 421        if bfs:
 422            yield from self.bfs(prune=prune)
 423        else:
 424            yield from self.dfs(prune=prune)
 425
 426    def dfs(self, parent=None, key=None, prune=None):
 427        """
 428        Returns a generator object which visits all nodes in this tree in
 429        the DFS (Depth-first) order.
 430
 431        Returns:
 432            The generator object.
 433        """
 434        parent = parent or self.parent
 435        yield self, parent, key
 436        if prune and prune(self, parent, key):
 437            return
 438
 439        for k, v in self.iter_expressions():
 440            yield from v.dfs(self, k, prune)
 441
 442    def bfs(self, prune=None):
 443        """
 444        Returns a generator object which visits all nodes in this tree in
 445        the BFS (Breadth-first) order.
 446
 447        Returns:
 448            The generator object.
 449        """
 450        queue = deque([(self, self.parent, None)])
 451
 452        while queue:
 453            item, parent, key = queue.popleft()
 454
 455            yield item, parent, key
 456            if prune and prune(item, parent, key):
 457                continue
 458
 459            for k, v in item.iter_expressions():
 460                queue.append((v, item, k))
 461
 462    def unnest(self):
 463        """
 464        Returns the first non parenthesis child or self.
 465        """
 466        expression = self
 467        while type(expression) is Paren:
 468            expression = expression.this
 469        return expression
 470
 471    def unalias(self):
 472        """
 473        Returns the inner expression if this is an Alias.
 474        """
 475        if isinstance(self, Alias):
 476            return self.this
 477        return self
 478
 479    def unnest_operands(self):
 480        """
 481        Returns unnested operands as a tuple.
 482        """
 483        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 484
 485    def flatten(self, unnest=True):
 486        """
 487        Returns a generator which yields child nodes who's parents are the same class.
 488
 489        A AND B AND C -> [A, B, C]
 490        """
 491        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 492            if not type(node) is self.__class__:
 493                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 494
 495    def __str__(self) -> str:
 496        return self.sql()
 497
 498    def __repr__(self) -> str:
 499        return self._to_s()
 500
 501    def sql(self, dialect: DialectType = None, **opts) -> str:
 502        """
 503        Returns SQL string representation of this tree.
 504
 505        Args:
 506            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 507            opts: other `sqlglot.generator.Generator` options.
 508
 509        Returns:
 510            The SQL string.
 511        """
 512        from sqlglot.dialects import Dialect
 513
 514        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 515
 516    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 517        indent = "" if not level else "\n"
 518        indent += "".join(["  "] * level)
 519        left = f"({self.key.upper()} "
 520
 521        args: t.Dict[str, t.Any] = {
 522            k: ", ".join(
 523                v._to_s(hide_missing=hide_missing, level=level + 1)
 524                if hasattr(v, "_to_s")
 525                else str(v)
 526                for v in ensure_list(vs)
 527                if v is not None
 528            )
 529            for k, vs in self.args.items()
 530        }
 531        args["comments"] = self.comments
 532        args["type"] = self.type
 533        args = {k: v for k, v in args.items() if v or not hide_missing}
 534
 535        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 536        right += ")"
 537
 538        return indent + left + right
 539
 540    def transform(self, fun, *args, copy=True, **kwargs):
 541        """
 542        Recursively visits all tree nodes (excluding already transformed ones)
 543        and applies the given transformation function to each node.
 544
 545        Args:
 546            fun (function): a function which takes a node as an argument and returns a
 547                new transformed node or the same node without modifications. If the function
 548                returns None, then the corresponding node will be removed from the syntax tree.
 549            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 550                modified in place.
 551
 552        Returns:
 553            The transformed tree.
 554        """
 555        node = self.copy() if copy else self
 556        new_node = fun(node, *args, **kwargs)
 557
 558        if new_node is None or not isinstance(new_node, Expression):
 559            return new_node
 560        if new_node is not node:
 561            new_node.parent = node.parent
 562            return new_node
 563
 564        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 565        return new_node
 566
 567    @t.overload
 568    def replace(self, expression: E) -> E:
 569        ...
 570
 571    @t.overload
 572    def replace(self, expression: None) -> None:
 573        ...
 574
 575    def replace(self, expression):
 576        """
 577        Swap out this expression with a new expression.
 578
 579        For example::
 580
 581            >>> tree = Select().select("x").from_("tbl")
 582            >>> tree.find(Column).replace(Column(this="y"))
 583            (COLUMN this: y)
 584            >>> tree.sql()
 585            'SELECT y FROM tbl'
 586
 587        Args:
 588            expression: new node
 589
 590        Returns:
 591            The new expression or expressions.
 592        """
 593        if not self.parent:
 594            return expression
 595
 596        parent = self.parent
 597        self.parent = None
 598
 599        replace_children(parent, lambda child: expression if child is self else child)
 600        return expression
 601
 602    def pop(self: E) -> E:
 603        """
 604        Remove this expression from its AST.
 605
 606        Returns:
 607            The popped expression.
 608        """
 609        self.replace(None)
 610        return self
 611
 612    def assert_is(self, type_: t.Type[E]) -> E:
 613        """
 614        Assert that this `Expression` is an instance of `type_`.
 615
 616        If it is NOT an instance of `type_`, this raises an assertion error.
 617        Otherwise, this returns this expression.
 618
 619        Examples:
 620            This is useful for type security in chained expressions:
 621
 622            >>> import sqlglot
 623            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 624            'SELECT x, z FROM y'
 625        """
 626        assert isinstance(self, type_)
 627        return self
 628
 629    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 630        """
 631        Checks if this expression is valid (e.g. all mandatory args are set).
 632
 633        Args:
 634            args: a sequence of values that were used to instantiate a Func expression. This is used
 635                to check that the provided arguments don't exceed the function argument limit.
 636
 637        Returns:
 638            A list of error messages for all possible errors that were found.
 639        """
 640        errors: t.List[str] = []
 641
 642        for k in self.args:
 643            if k not in self.arg_types:
 644                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 645        for k, mandatory in self.arg_types.items():
 646            v = self.args.get(k)
 647            if mandatory and (v is None or (isinstance(v, list) and not v)):
 648                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 649
 650        if (
 651            args
 652            and isinstance(self, Func)
 653            and len(args) > len(self.arg_types)
 654            and not self.is_var_len_args
 655        ):
 656            errors.append(
 657                f"The number of provided arguments ({len(args)}) is greater than "
 658                f"the maximum number of supported arguments ({len(self.arg_types)})"
 659            )
 660
 661        return errors
 662
 663    def dump(self):
 664        """
 665        Dump this Expression to a JSON-serializable dict.
 666        """
 667        from sqlglot.serde import dump
 668
 669        return dump(self)
 670
 671    @classmethod
 672    def load(cls, obj):
 673        """
 674        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 675        """
 676        from sqlglot.serde import load
 677
 678        return load(obj)
 679
 680    def and_(
 681        self,
 682        *expressions: t.Optional[ExpOrStr],
 683        dialect: DialectType = None,
 684        copy: bool = True,
 685        **opts,
 686    ) -> Condition:
 687        """
 688        AND this condition with one or multiple expressions.
 689
 690        Example:
 691            >>> condition("x=1").and_("y=1").sql()
 692            'x = 1 AND y = 1'
 693
 694        Args:
 695            *expressions: the SQL code strings to parse.
 696                If an `Expression` instance is passed, it will be used as-is.
 697            dialect: the dialect used to parse the input expression.
 698            copy: whether or not to copy the involved expressions (only applies to Expressions).
 699            opts: other options to use to parse the input expressions.
 700
 701        Returns:
 702            The new And condition.
 703        """
 704        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 705
 706    def or_(
 707        self,
 708        *expressions: t.Optional[ExpOrStr],
 709        dialect: DialectType = None,
 710        copy: bool = True,
 711        **opts,
 712    ) -> Condition:
 713        """
 714        OR this condition with one or multiple expressions.
 715
 716        Example:
 717            >>> condition("x=1").or_("y=1").sql()
 718            'x = 1 OR y = 1'
 719
 720        Args:
 721            *expressions: the SQL code strings to parse.
 722                If an `Expression` instance is passed, it will be used as-is.
 723            dialect: the dialect used to parse the input expression.
 724            copy: whether or not to copy the involved expressions (only applies to Expressions).
 725            opts: other options to use to parse the input expressions.
 726
 727        Returns:
 728            The new Or condition.
 729        """
 730        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 731
 732    def not_(self, copy: bool = True):
 733        """
 734        Wrap this condition with NOT.
 735
 736        Example:
 737            >>> condition("x=1").not_().sql()
 738            'NOT x = 1'
 739
 740        Args:
 741            copy: whether or not to copy this object.
 742
 743        Returns:
 744            The new Not instance.
 745        """
 746        return not_(self, copy=copy)
 747
 748    def as_(
 749        self,
 750        alias: str | Identifier,
 751        quoted: t.Optional[bool] = None,
 752        dialect: DialectType = None,
 753        copy: bool = True,
 754        **opts,
 755    ) -> Alias:
 756        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 757
 758    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 759        this = self.copy()
 760        other = convert(other, copy=True)
 761        if not isinstance(this, klass) and not isinstance(other, klass):
 762            this = _wrap(this, Binary)
 763            other = _wrap(other, Binary)
 764        if reverse:
 765            return klass(this=other, expression=this)
 766        return klass(this=this, expression=other)
 767
 768    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 769        return Bracket(
 770            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 771        )
 772
 773    def __iter__(self) -> t.Iterator:
 774        if "expressions" in self.arg_types:
 775            return iter(self.args.get("expressions") or [])
 776        # We define this because __getitem__ converts Expression into an iterable, which is
 777        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 778        # See: https://peps.python.org/pep-0234/
 779        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 780
 781    def isin(
 782        self,
 783        *expressions: t.Any,
 784        query: t.Optional[ExpOrStr] = None,
 785        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 786        copy: bool = True,
 787        **opts,
 788    ) -> In:
 789        return In(
 790            this=maybe_copy(self, copy),
 791            expressions=[convert(e, copy=copy) for e in expressions],
 792            query=maybe_parse(query, copy=copy, **opts) if query else None,
 793            unnest=Unnest(
 794                expressions=[
 795                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 796                ]
 797            )
 798            if unnest
 799            else None,
 800        )
 801
 802    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 803        return Between(
 804            this=maybe_copy(self, copy),
 805            low=convert(low, copy=copy, **opts),
 806            high=convert(high, copy=copy, **opts),
 807        )
 808
 809    def is_(self, other: ExpOrStr) -> Is:
 810        return self._binop(Is, other)
 811
 812    def like(self, other: ExpOrStr) -> Like:
 813        return self._binop(Like, other)
 814
 815    def ilike(self, other: ExpOrStr) -> ILike:
 816        return self._binop(ILike, other)
 817
 818    def eq(self, other: t.Any) -> EQ:
 819        return self._binop(EQ, other)
 820
 821    def neq(self, other: t.Any) -> NEQ:
 822        return self._binop(NEQ, other)
 823
 824    def rlike(self, other: ExpOrStr) -> RegexpLike:
 825        return self._binop(RegexpLike, other)
 826
 827    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 828        div = self._binop(Div, other)
 829        div.args["typed"] = typed
 830        div.args["safe"] = safe
 831        return div
 832
 833    def __lt__(self, other: t.Any) -> LT:
 834        return self._binop(LT, other)
 835
 836    def __le__(self, other: t.Any) -> LTE:
 837        return self._binop(LTE, other)
 838
 839    def __gt__(self, other: t.Any) -> GT:
 840        return self._binop(GT, other)
 841
 842    def __ge__(self, other: t.Any) -> GTE:
 843        return self._binop(GTE, other)
 844
 845    def __add__(self, other: t.Any) -> Add:
 846        return self._binop(Add, other)
 847
 848    def __radd__(self, other: t.Any) -> Add:
 849        return self._binop(Add, other, reverse=True)
 850
 851    def __sub__(self, other: t.Any) -> Sub:
 852        return self._binop(Sub, other)
 853
 854    def __rsub__(self, other: t.Any) -> Sub:
 855        return self._binop(Sub, other, reverse=True)
 856
 857    def __mul__(self, other: t.Any) -> Mul:
 858        return self._binop(Mul, other)
 859
 860    def __rmul__(self, other: t.Any) -> Mul:
 861        return self._binop(Mul, other, reverse=True)
 862
 863    def __truediv__(self, other: t.Any) -> Div:
 864        return self._binop(Div, other)
 865
 866    def __rtruediv__(self, other: t.Any) -> Div:
 867        return self._binop(Div, other, reverse=True)
 868
 869    def __floordiv__(self, other: t.Any) -> IntDiv:
 870        return self._binop(IntDiv, other)
 871
 872    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 873        return self._binop(IntDiv, other, reverse=True)
 874
 875    def __mod__(self, other: t.Any) -> Mod:
 876        return self._binop(Mod, other)
 877
 878    def __rmod__(self, other: t.Any) -> Mod:
 879        return self._binop(Mod, other, reverse=True)
 880
 881    def __pow__(self, other: t.Any) -> Pow:
 882        return self._binop(Pow, other)
 883
 884    def __rpow__(self, other: t.Any) -> Pow:
 885        return self._binop(Pow, other, reverse=True)
 886
 887    def __and__(self, other: t.Any) -> And:
 888        return self._binop(And, other)
 889
 890    def __rand__(self, other: t.Any) -> And:
 891        return self._binop(And, other, reverse=True)
 892
 893    def __or__(self, other: t.Any) -> Or:
 894        return self._binop(Or, other)
 895
 896    def __ror__(self, other: t.Any) -> Or:
 897        return self._binop(Or, other, reverse=True)
 898
 899    def __neg__(self) -> Neg:
 900        return Neg(this=_wrap(self.copy(), Binary))
 901
 902    def __invert__(self) -> Not:
 903        return not_(self.copy())
 904
 905
 906IntoType = t.Union[
 907    str,
 908    t.Type[Expression],
 909    t.Collection[t.Union[str, t.Type[Expression]]],
 910]
 911ExpOrStr = t.Union[str, Expression]
 912
 913
 914class Condition(Expression):
 915    """Logical conditions like x AND y, or simply x"""
 916
 917
 918class Predicate(Condition):
 919    """Relationships like x = y, x > 1, x >= y."""
 920
 921
 922class DerivedTable(Expression):
 923    @property
 924    def selects(self) -> t.List[Expression]:
 925        return self.this.selects if isinstance(self.this, Subqueryable) else []
 926
 927    @property
 928    def named_selects(self) -> t.List[str]:
 929        return [select.output_name for select in self.selects]
 930
 931
 932class Unionable(Expression):
 933    def union(
 934        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 935    ) -> Unionable:
 936        """
 937        Builds a UNION expression.
 938
 939        Example:
 940            >>> import sqlglot
 941            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 942            'SELECT * FROM foo UNION SELECT * FROM bla'
 943
 944        Args:
 945            expression: the SQL code string.
 946                If an `Expression` instance is passed, it will be used as-is.
 947            distinct: set the DISTINCT flag if and only if this is true.
 948            dialect: the dialect used to parse the input expression.
 949            opts: other options to use to parse the input expressions.
 950
 951        Returns:
 952            The new Union expression.
 953        """
 954        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 955
 956    def intersect(
 957        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 958    ) -> Unionable:
 959        """
 960        Builds an INTERSECT expression.
 961
 962        Example:
 963            >>> import sqlglot
 964            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 965            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 966
 967        Args:
 968            expression: the SQL code string.
 969                If an `Expression` instance is passed, it will be used as-is.
 970            distinct: set the DISTINCT flag if and only if this is true.
 971            dialect: the dialect used to parse the input expression.
 972            opts: other options to use to parse the input expressions.
 973
 974        Returns:
 975            The new Intersect expression.
 976        """
 977        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 978
 979    def except_(
 980        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 981    ) -> Unionable:
 982        """
 983        Builds an EXCEPT expression.
 984
 985        Example:
 986            >>> import sqlglot
 987            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 988            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 989
 990        Args:
 991            expression: the SQL code string.
 992                If an `Expression` instance is passed, it will be used as-is.
 993            distinct: set the DISTINCT flag if and only if this is true.
 994            dialect: the dialect used to parse the input expression.
 995            opts: other options to use to parse the input expressions.
 996
 997        Returns:
 998            The new Except expression.
 999        """
1000        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1001
1002
1003class UDTF(DerivedTable, Unionable):
1004    @property
1005    def selects(self) -> t.List[Expression]:
1006        alias = self.args.get("alias")
1007        return alias.columns if alias else []
1008
1009
1010class Cache(Expression):
1011    arg_types = {
1012        "this": True,
1013        "lazy": False,
1014        "options": False,
1015        "expression": False,
1016    }
1017
1018
1019class Uncache(Expression):
1020    arg_types = {"this": True, "exists": False}
1021
1022
1023class Refresh(Expression):
1024    pass
1025
1026
1027class DDL(Expression):
1028    @property
1029    def ctes(self):
1030        with_ = self.args.get("with")
1031        if not with_:
1032            return []
1033        return with_.expressions
1034
1035    @property
1036    def named_selects(self) -> t.List[str]:
1037        if isinstance(self.expression, Subqueryable):
1038            return self.expression.named_selects
1039        return []
1040
1041    @property
1042    def selects(self) -> t.List[Expression]:
1043        if isinstance(self.expression, Subqueryable):
1044            return self.expression.selects
1045        return []
1046
1047
1048class Create(DDL):
1049    arg_types = {
1050        "with": False,
1051        "this": True,
1052        "kind": True,
1053        "expression": False,
1054        "exists": False,
1055        "properties": False,
1056        "replace": False,
1057        "unique": False,
1058        "indexes": False,
1059        "no_schema_binding": False,
1060        "begin": False,
1061        "end": False,
1062        "clone": False,
1063    }
1064
1065
1066# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1067# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1068# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1069class Clone(Expression):
1070    arg_types = {
1071        "this": True,
1072        "when": False,
1073        "kind": False,
1074        "shallow": False,
1075        "expression": False,
1076        "copy": False,
1077    }
1078
1079
1080class Describe(Expression):
1081    arg_types = {"this": True, "kind": False, "expressions": False}
1082
1083
1084class Kill(Expression):
1085    arg_types = {"this": True, "kind": False}
1086
1087
1088class Pragma(Expression):
1089    pass
1090
1091
1092class Set(Expression):
1093    arg_types = {"expressions": False, "unset": False, "tag": False}
1094
1095
1096class SetItem(Expression):
1097    arg_types = {
1098        "this": False,
1099        "expressions": False,
1100        "kind": False,
1101        "collate": False,  # MySQL SET NAMES statement
1102        "global": False,
1103    }
1104
1105
1106class Show(Expression):
1107    arg_types = {
1108        "this": True,
1109        "target": False,
1110        "offset": False,
1111        "limit": False,
1112        "like": False,
1113        "where": False,
1114        "db": False,
1115        "scope": False,
1116        "scope_kind": False,
1117        "full": False,
1118        "mutex": False,
1119        "query": False,
1120        "channel": False,
1121        "global": False,
1122        "log": False,
1123        "position": False,
1124        "types": False,
1125    }
1126
1127
1128class UserDefinedFunction(Expression):
1129    arg_types = {"this": True, "expressions": False, "wrapped": False}
1130
1131
1132class CharacterSet(Expression):
1133    arg_types = {"this": True, "default": False}
1134
1135
1136class With(Expression):
1137    arg_types = {"expressions": True, "recursive": False}
1138
1139    @property
1140    def recursive(self) -> bool:
1141        return bool(self.args.get("recursive"))
1142
1143
1144class WithinGroup(Expression):
1145    arg_types = {"this": True, "expression": False}
1146
1147
1148class CTE(DerivedTable):
1149    arg_types = {"this": True, "alias": True}
1150
1151
1152class TableAlias(Expression):
1153    arg_types = {"this": False, "columns": False}
1154
1155    @property
1156    def columns(self):
1157        return self.args.get("columns") or []
1158
1159
1160class BitString(Condition):
1161    pass
1162
1163
1164class HexString(Condition):
1165    pass
1166
1167
1168class ByteString(Condition):
1169    pass
1170
1171
1172class RawString(Condition):
1173    pass
1174
1175
1176class Column(Condition):
1177    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1178
1179    @property
1180    def table(self) -> str:
1181        return self.text("table")
1182
1183    @property
1184    def db(self) -> str:
1185        return self.text("db")
1186
1187    @property
1188    def catalog(self) -> str:
1189        return self.text("catalog")
1190
1191    @property
1192    def output_name(self) -> str:
1193        return self.name
1194
1195    @property
1196    def parts(self) -> t.List[Identifier]:
1197        """Return the parts of a column in order catalog, db, table, name."""
1198        return [
1199            t.cast(Identifier, self.args[part])
1200            for part in ("catalog", "db", "table", "this")
1201            if self.args.get(part)
1202        ]
1203
1204    def to_dot(self) -> Dot | Identifier:
1205        """Converts the column into a dot expression."""
1206        parts = self.parts
1207        parent = self.parent
1208
1209        while parent:
1210            if isinstance(parent, Dot):
1211                parts.append(parent.expression)
1212            parent = parent.parent
1213
1214        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1215
1216
1217class ColumnPosition(Expression):
1218    arg_types = {"this": False, "position": True}
1219
1220
1221class ColumnDef(Expression):
1222    arg_types = {
1223        "this": True,
1224        "kind": False,
1225        "constraints": False,
1226        "exists": False,
1227        "position": False,
1228    }
1229
1230    @property
1231    def constraints(self) -> t.List[ColumnConstraint]:
1232        return self.args.get("constraints") or []
1233
1234
1235class AlterColumn(Expression):
1236    arg_types = {
1237        "this": True,
1238        "dtype": False,
1239        "collate": False,
1240        "using": False,
1241        "default": False,
1242        "drop": False,
1243    }
1244
1245
1246class RenameTable(Expression):
1247    pass
1248
1249
1250class SwapTable(Expression):
1251    pass
1252
1253
1254class Comment(Expression):
1255    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1256
1257
1258class Comprehension(Expression):
1259    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1260
1261
1262# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1263class MergeTreeTTLAction(Expression):
1264    arg_types = {
1265        "this": True,
1266        "delete": False,
1267        "recompress": False,
1268        "to_disk": False,
1269        "to_volume": False,
1270    }
1271
1272
1273# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1274class MergeTreeTTL(Expression):
1275    arg_types = {
1276        "expressions": True,
1277        "where": False,
1278        "group": False,
1279        "aggregates": False,
1280    }
1281
1282
1283# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1284class IndexConstraintOption(Expression):
1285    arg_types = {
1286        "key_block_size": False,
1287        "using": False,
1288        "parser": False,
1289        "comment": False,
1290        "visible": False,
1291        "engine_attr": False,
1292        "secondary_engine_attr": False,
1293    }
1294
1295
1296class ColumnConstraint(Expression):
1297    arg_types = {"this": False, "kind": True}
1298
1299    @property
1300    def kind(self) -> ColumnConstraintKind:
1301        return self.args["kind"]
1302
1303
1304class ColumnConstraintKind(Expression):
1305    pass
1306
1307
1308class AutoIncrementColumnConstraint(ColumnConstraintKind):
1309    pass
1310
1311
1312class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1313    arg_types = {"this": True, "expression": True}
1314
1315
1316class CaseSpecificColumnConstraint(ColumnConstraintKind):
1317    arg_types = {"not_": True}
1318
1319
1320class CharacterSetColumnConstraint(ColumnConstraintKind):
1321    arg_types = {"this": True}
1322
1323
1324class CheckColumnConstraint(ColumnConstraintKind):
1325    pass
1326
1327
1328class ClusteredColumnConstraint(ColumnConstraintKind):
1329    pass
1330
1331
1332class CollateColumnConstraint(ColumnConstraintKind):
1333    pass
1334
1335
1336class CommentColumnConstraint(ColumnConstraintKind):
1337    pass
1338
1339
1340class CompressColumnConstraint(ColumnConstraintKind):
1341    pass
1342
1343
1344class DateFormatColumnConstraint(ColumnConstraintKind):
1345    arg_types = {"this": True}
1346
1347
1348class DefaultColumnConstraint(ColumnConstraintKind):
1349    pass
1350
1351
1352class EncodeColumnConstraint(ColumnConstraintKind):
1353    pass
1354
1355
1356class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1357    # this: True -> ALWAYS, this: False -> BY DEFAULT
1358    arg_types = {
1359        "this": False,
1360        "expression": False,
1361        "on_null": False,
1362        "start": False,
1363        "increment": False,
1364        "minvalue": False,
1365        "maxvalue": False,
1366        "cycle": False,
1367    }
1368
1369
1370class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1371    arg_types = {"start": True, "hidden": False}
1372
1373
1374# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1375class IndexColumnConstraint(ColumnConstraintKind):
1376    arg_types = {
1377        "this": False,
1378        "schema": True,
1379        "kind": False,
1380        "index_type": False,
1381        "options": False,
1382    }
1383
1384
1385class InlineLengthColumnConstraint(ColumnConstraintKind):
1386    pass
1387
1388
1389class NonClusteredColumnConstraint(ColumnConstraintKind):
1390    pass
1391
1392
1393class NotForReplicationColumnConstraint(ColumnConstraintKind):
1394    arg_types = {}
1395
1396
1397class NotNullColumnConstraint(ColumnConstraintKind):
1398    arg_types = {"allow_null": False}
1399
1400
1401# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1402class OnUpdateColumnConstraint(ColumnConstraintKind):
1403    pass
1404
1405
1406class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1407    arg_types = {"desc": False}
1408
1409
1410class TitleColumnConstraint(ColumnConstraintKind):
1411    pass
1412
1413
1414class UniqueColumnConstraint(ColumnConstraintKind):
1415    arg_types = {"this": False, "index_type": False}
1416
1417
1418class UppercaseColumnConstraint(ColumnConstraintKind):
1419    arg_types: t.Dict[str, t.Any] = {}
1420
1421
1422class PathColumnConstraint(ColumnConstraintKind):
1423    pass
1424
1425
1426# computed column expression
1427# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1428class ComputedColumnConstraint(ColumnConstraintKind):
1429    arg_types = {"this": True, "persisted": False, "not_null": False}
1430
1431
1432class Constraint(Expression):
1433    arg_types = {"this": True, "expressions": True}
1434
1435
1436class Delete(Expression):
1437    arg_types = {
1438        "with": False,
1439        "this": False,
1440        "using": False,
1441        "where": False,
1442        "returning": False,
1443        "limit": False,
1444        "tables": False,  # Multiple-Table Syntax (MySQL)
1445    }
1446
1447    def delete(
1448        self,
1449        table: ExpOrStr,
1450        dialect: DialectType = None,
1451        copy: bool = True,
1452        **opts,
1453    ) -> Delete:
1454        """
1455        Create a DELETE expression or replace the table on an existing DELETE expression.
1456
1457        Example:
1458            >>> delete("tbl").sql()
1459            'DELETE FROM tbl'
1460
1461        Args:
1462            table: the table from which to delete.
1463            dialect: the dialect used to parse the input expression.
1464            copy: if `False`, modify this expression instance in-place.
1465            opts: other options to use to parse the input expressions.
1466
1467        Returns:
1468            Delete: the modified expression.
1469        """
1470        return _apply_builder(
1471            expression=table,
1472            instance=self,
1473            arg="this",
1474            dialect=dialect,
1475            into=Table,
1476            copy=copy,
1477            **opts,
1478        )
1479
1480    def where(
1481        self,
1482        *expressions: t.Optional[ExpOrStr],
1483        append: bool = True,
1484        dialect: DialectType = None,
1485        copy: bool = True,
1486        **opts,
1487    ) -> Delete:
1488        """
1489        Append to or set the WHERE expressions.
1490
1491        Example:
1492            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1493            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1494
1495        Args:
1496            *expressions: the SQL code strings to parse.
1497                If an `Expression` instance is passed, it will be used as-is.
1498                Multiple expressions are combined with an AND operator.
1499            append: if `True`, AND the new expressions to any existing expression.
1500                Otherwise, this resets the expression.
1501            dialect: the dialect used to parse the input expressions.
1502            copy: if `False`, modify this expression instance in-place.
1503            opts: other options to use to parse the input expressions.
1504
1505        Returns:
1506            Delete: the modified expression.
1507        """
1508        return _apply_conjunction_builder(
1509            *expressions,
1510            instance=self,
1511            arg="where",
1512            append=append,
1513            into=Where,
1514            dialect=dialect,
1515            copy=copy,
1516            **opts,
1517        )
1518
1519    def returning(
1520        self,
1521        expression: ExpOrStr,
1522        dialect: DialectType = None,
1523        copy: bool = True,
1524        **opts,
1525    ) -> Delete:
1526        """
1527        Set the RETURNING expression. Not supported by all dialects.
1528
1529        Example:
1530            >>> delete("tbl").returning("*", dialect="postgres").sql()
1531            'DELETE FROM tbl RETURNING *'
1532
1533        Args:
1534            expression: the SQL code strings to parse.
1535                If an `Expression` instance is passed, it will be used as-is.
1536            dialect: the dialect used to parse the input expressions.
1537            copy: if `False`, modify this expression instance in-place.
1538            opts: other options to use to parse the input expressions.
1539
1540        Returns:
1541            Delete: the modified expression.
1542        """
1543        return _apply_builder(
1544            expression=expression,
1545            instance=self,
1546            arg="returning",
1547            prefix="RETURNING",
1548            dialect=dialect,
1549            copy=copy,
1550            into=Returning,
1551            **opts,
1552        )
1553
1554
1555class Drop(Expression):
1556    arg_types = {
1557        "this": False,
1558        "kind": False,
1559        "exists": False,
1560        "temporary": False,
1561        "materialized": False,
1562        "cascade": False,
1563        "constraints": False,
1564        "purge": False,
1565    }
1566
1567
1568class Filter(Expression):
1569    arg_types = {"this": True, "expression": True}
1570
1571
1572class Check(Expression):
1573    pass
1574
1575
1576# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1577class Connect(Expression):
1578    arg_types = {"start": False, "connect": True}
1579
1580
1581class Prior(Expression):
1582    pass
1583
1584
1585class Directory(Expression):
1586    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1587    arg_types = {"this": True, "local": False, "row_format": False}
1588
1589
1590class ForeignKey(Expression):
1591    arg_types = {
1592        "expressions": True,
1593        "reference": False,
1594        "delete": False,
1595        "update": False,
1596    }
1597
1598
1599class ColumnPrefix(Expression):
1600    arg_types = {"this": True, "expression": True}
1601
1602
1603class PrimaryKey(Expression):
1604    arg_types = {"expressions": True, "options": False}
1605
1606
1607# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1608# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1609class Into(Expression):
1610    arg_types = {"this": True, "temporary": False, "unlogged": False}
1611
1612
1613class From(Expression):
1614    @property
1615    def name(self) -> str:
1616        return self.this.name
1617
1618    @property
1619    def alias_or_name(self) -> str:
1620        return self.this.alias_or_name
1621
1622
1623class Having(Expression):
1624    pass
1625
1626
1627class Hint(Expression):
1628    arg_types = {"expressions": True}
1629
1630
1631class JoinHint(Expression):
1632    arg_types = {"this": True, "expressions": True}
1633
1634
1635class Identifier(Expression):
1636    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1637
1638    @property
1639    def quoted(self) -> bool:
1640        return bool(self.args.get("quoted"))
1641
1642    @property
1643    def hashable_args(self) -> t.Any:
1644        return (self.this, self.quoted)
1645
1646    @property
1647    def output_name(self) -> str:
1648        return self.name
1649
1650
1651# https://www.postgresql.org/docs/current/indexes-opclass.html
1652class Opclass(Expression):
1653    arg_types = {"this": True, "expression": True}
1654
1655
1656class Index(Expression):
1657    arg_types = {
1658        "this": False,
1659        "table": False,
1660        "using": False,
1661        "where": False,
1662        "columns": False,
1663        "unique": False,
1664        "primary": False,
1665        "amp": False,  # teradata
1666        "partition_by": False,  # teradata
1667        "where": False,  # postgres partial indexes
1668    }
1669
1670
1671class Insert(DDL):
1672    arg_types = {
1673        "with": False,
1674        "this": True,
1675        "expression": False,
1676        "conflict": False,
1677        "returning": False,
1678        "overwrite": False,
1679        "exists": False,
1680        "partition": False,
1681        "alternative": False,
1682        "where": False,
1683        "ignore": False,
1684        "by_name": False,
1685    }
1686
1687    def with_(
1688        self,
1689        alias: ExpOrStr,
1690        as_: ExpOrStr,
1691        recursive: t.Optional[bool] = None,
1692        append: bool = True,
1693        dialect: DialectType = None,
1694        copy: bool = True,
1695        **opts,
1696    ) -> Insert:
1697        """
1698        Append to or set the common table expressions.
1699
1700        Example:
1701            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1702            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1703
1704        Args:
1705            alias: the SQL code string to parse as the table name.
1706                If an `Expression` instance is passed, this is used as-is.
1707            as_: the SQL code string to parse as the table expression.
1708                If an `Expression` instance is passed, it will be used as-is.
1709            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1710            append: if `True`, add to any existing expressions.
1711                Otherwise, this resets the expressions.
1712            dialect: the dialect used to parse the input expression.
1713            copy: if `False`, modify this expression instance in-place.
1714            opts: other options to use to parse the input expressions.
1715
1716        Returns:
1717            The modified expression.
1718        """
1719        return _apply_cte_builder(
1720            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1721        )
1722
1723
1724class OnConflict(Expression):
1725    arg_types = {
1726        "duplicate": False,
1727        "expressions": False,
1728        "nothing": False,
1729        "key": False,
1730        "constraint": False,
1731    }
1732
1733
1734class Returning(Expression):
1735    arg_types = {"expressions": True, "into": False}
1736
1737
1738# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1739class Introducer(Expression):
1740    arg_types = {"this": True, "expression": True}
1741
1742
1743# national char, like n'utf8'
1744class National(Expression):
1745    pass
1746
1747
1748class LoadData(Expression):
1749    arg_types = {
1750        "this": True,
1751        "local": False,
1752        "overwrite": False,
1753        "inpath": True,
1754        "partition": False,
1755        "input_format": False,
1756        "serde": False,
1757    }
1758
1759
1760class Partition(Expression):
1761    arg_types = {"expressions": True}
1762
1763
1764class Fetch(Expression):
1765    arg_types = {
1766        "direction": False,
1767        "count": False,
1768        "percent": False,
1769        "with_ties": False,
1770    }
1771
1772
1773class Group(Expression):
1774    arg_types = {
1775        "expressions": False,
1776        "grouping_sets": False,
1777        "cube": False,
1778        "rollup": False,
1779        "totals": False,
1780        "all": False,
1781    }
1782
1783
1784class Lambda(Expression):
1785    arg_types = {"this": True, "expressions": True}
1786
1787
1788class Limit(Expression):
1789    arg_types = {"this": False, "expression": True, "offset": False}
1790
1791
1792class Literal(Condition):
1793    arg_types = {"this": True, "is_string": True}
1794
1795    @property
1796    def hashable_args(self) -> t.Any:
1797        return (self.this, self.args.get("is_string"))
1798
1799    @classmethod
1800    def number(cls, number) -> Literal:
1801        return cls(this=str(number), is_string=False)
1802
1803    @classmethod
1804    def string(cls, string) -> Literal:
1805        return cls(this=str(string), is_string=True)
1806
1807    @property
1808    def output_name(self) -> str:
1809        return self.name
1810
1811
1812class Join(Expression):
1813    arg_types = {
1814        "this": True,
1815        "on": False,
1816        "side": False,
1817        "kind": False,
1818        "using": False,
1819        "method": False,
1820        "global": False,
1821        "hint": False,
1822    }
1823
1824    @property
1825    def method(self) -> str:
1826        return self.text("method").upper()
1827
1828    @property
1829    def kind(self) -> str:
1830        return self.text("kind").upper()
1831
1832    @property
1833    def side(self) -> str:
1834        return self.text("side").upper()
1835
1836    @property
1837    def hint(self) -> str:
1838        return self.text("hint").upper()
1839
1840    @property
1841    def alias_or_name(self) -> str:
1842        return self.this.alias_or_name
1843
1844    def on(
1845        self,
1846        *expressions: t.Optional[ExpOrStr],
1847        append: bool = True,
1848        dialect: DialectType = None,
1849        copy: bool = True,
1850        **opts,
1851    ) -> Join:
1852        """
1853        Append to or set the ON expressions.
1854
1855        Example:
1856            >>> import sqlglot
1857            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1858            'JOIN x ON y = 1'
1859
1860        Args:
1861            *expressions: the SQL code strings to parse.
1862                If an `Expression` instance is passed, it will be used as-is.
1863                Multiple expressions are combined with an AND operator.
1864            append: if `True`, AND the new expressions to any existing expression.
1865                Otherwise, this resets the expression.
1866            dialect: the dialect used to parse the input expressions.
1867            copy: if `False`, modify this expression instance in-place.
1868            opts: other options to use to parse the input expressions.
1869
1870        Returns:
1871            The modified Join expression.
1872        """
1873        join = _apply_conjunction_builder(
1874            *expressions,
1875            instance=self,
1876            arg="on",
1877            append=append,
1878            dialect=dialect,
1879            copy=copy,
1880            **opts,
1881        )
1882
1883        if join.kind == "CROSS":
1884            join.set("kind", None)
1885
1886        return join
1887
1888    def using(
1889        self,
1890        *expressions: t.Optional[ExpOrStr],
1891        append: bool = True,
1892        dialect: DialectType = None,
1893        copy: bool = True,
1894        **opts,
1895    ) -> Join:
1896        """
1897        Append to or set the USING expressions.
1898
1899        Example:
1900            >>> import sqlglot
1901            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1902            'JOIN x USING (foo, bla)'
1903
1904        Args:
1905            *expressions: the SQL code strings to parse.
1906                If an `Expression` instance is passed, it will be used as-is.
1907            append: if `True`, concatenate the new expressions to the existing "using" list.
1908                Otherwise, this resets the expression.
1909            dialect: the dialect used to parse the input expressions.
1910            copy: if `False`, modify this expression instance in-place.
1911            opts: other options to use to parse the input expressions.
1912
1913        Returns:
1914            The modified Join expression.
1915        """
1916        join = _apply_list_builder(
1917            *expressions,
1918            instance=self,
1919            arg="using",
1920            append=append,
1921            dialect=dialect,
1922            copy=copy,
1923            **opts,
1924        )
1925
1926        if join.kind == "CROSS":
1927            join.set("kind", None)
1928
1929        return join
1930
1931
1932class Lateral(UDTF):
1933    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1934
1935
1936class MatchRecognize(Expression):
1937    arg_types = {
1938        "partition_by": False,
1939        "order": False,
1940        "measures": False,
1941        "rows": False,
1942        "after": False,
1943        "pattern": False,
1944        "define": False,
1945        "alias": False,
1946    }
1947
1948
1949# Clickhouse FROM FINAL modifier
1950# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1951class Final(Expression):
1952    pass
1953
1954
1955class Offset(Expression):
1956    arg_types = {"this": False, "expression": True}
1957
1958
1959class Order(Expression):
1960    arg_types = {"this": False, "expressions": True}
1961
1962
1963# hive specific sorts
1964# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1965class Cluster(Order):
1966    pass
1967
1968
1969class Distribute(Order):
1970    pass
1971
1972
1973class Sort(Order):
1974    pass
1975
1976
1977class Ordered(Expression):
1978    arg_types = {"this": True, "desc": False, "nulls_first": True}
1979
1980
1981class Property(Expression):
1982    arg_types = {"this": True, "value": True}
1983
1984
1985class AlgorithmProperty(Property):
1986    arg_types = {"this": True}
1987
1988
1989class AutoIncrementProperty(Property):
1990    arg_types = {"this": True}
1991
1992
1993class BlockCompressionProperty(Property):
1994    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1995
1996
1997class CharacterSetProperty(Property):
1998    arg_types = {"this": True, "default": True}
1999
2000
2001class ChecksumProperty(Property):
2002    arg_types = {"on": False, "default": False}
2003
2004
2005class CollateProperty(Property):
2006    arg_types = {"this": True, "default": False}
2007
2008
2009class CopyGrantsProperty(Property):
2010    arg_types = {}
2011
2012
2013class DataBlocksizeProperty(Property):
2014    arg_types = {
2015        "size": False,
2016        "units": False,
2017        "minimum": False,
2018        "maximum": False,
2019        "default": False,
2020    }
2021
2022
2023class DefinerProperty(Property):
2024    arg_types = {"this": True}
2025
2026
2027class DistKeyProperty(Property):
2028    arg_types = {"this": True}
2029
2030
2031class DistStyleProperty(Property):
2032    arg_types = {"this": True}
2033
2034
2035class EngineProperty(Property):
2036    arg_types = {"this": True}
2037
2038
2039class HeapProperty(Property):
2040    arg_types = {}
2041
2042
2043class ToTableProperty(Property):
2044    arg_types = {"this": True}
2045
2046
2047class ExecuteAsProperty(Property):
2048    arg_types = {"this": True}
2049
2050
2051class ExternalProperty(Property):
2052    arg_types = {"this": False}
2053
2054
2055class FallbackProperty(Property):
2056    arg_types = {"no": True, "protection": False}
2057
2058
2059class FileFormatProperty(Property):
2060    arg_types = {"this": True}
2061
2062
2063class FreespaceProperty(Property):
2064    arg_types = {"this": True, "percent": False}
2065
2066
2067class InputModelProperty(Property):
2068    arg_types = {"this": True}
2069
2070
2071class OutputModelProperty(Property):
2072    arg_types = {"this": True}
2073
2074
2075class IsolatedLoadingProperty(Property):
2076    arg_types = {
2077        "no": True,
2078        "concurrent": True,
2079        "for_all": True,
2080        "for_insert": True,
2081        "for_none": True,
2082    }
2083
2084
2085class JournalProperty(Property):
2086    arg_types = {
2087        "no": False,
2088        "dual": False,
2089        "before": False,
2090        "local": False,
2091        "after": False,
2092    }
2093
2094
2095class LanguageProperty(Property):
2096    arg_types = {"this": True}
2097
2098
2099# spark ddl
2100class ClusteredByProperty(Property):
2101    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2102
2103
2104class DictProperty(Property):
2105    arg_types = {"this": True, "kind": True, "settings": False}
2106
2107
2108class DictSubProperty(Property):
2109    pass
2110
2111
2112class DictRange(Property):
2113    arg_types = {"this": True, "min": True, "max": True}
2114
2115
2116# Clickhouse CREATE ... ON CLUSTER modifier
2117# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2118class OnCluster(Property):
2119    arg_types = {"this": True}
2120
2121
2122class LikeProperty(Property):
2123    arg_types = {"this": True, "expressions": False}
2124
2125
2126class LocationProperty(Property):
2127    arg_types = {"this": True}
2128
2129
2130class LockingProperty(Property):
2131    arg_types = {
2132        "this": False,
2133        "kind": True,
2134        "for_or_in": False,
2135        "lock_type": True,
2136        "override": False,
2137    }
2138
2139
2140class LogProperty(Property):
2141    arg_types = {"no": True}
2142
2143
2144class MaterializedProperty(Property):
2145    arg_types = {"this": False}
2146
2147
2148class MergeBlockRatioProperty(Property):
2149    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2150
2151
2152class NoPrimaryIndexProperty(Property):
2153    arg_types = {}
2154
2155
2156class OnProperty(Property):
2157    arg_types = {"this": True}
2158
2159
2160class OnCommitProperty(Property):
2161    arg_types = {"delete": False}
2162
2163
2164class PartitionedByProperty(Property):
2165    arg_types = {"this": True}
2166
2167
2168# https://www.postgresql.org/docs/current/sql-createtable.html
2169class PartitionBoundSpec(Expression):
2170    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2171    arg_types = {
2172        "this": False,
2173        "expression": False,
2174        "from_expressions": False,
2175        "to_expressions": False,
2176    }
2177
2178
2179class PartitionedOfProperty(Property):
2180    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2181    arg_types = {"this": True, "expression": True}
2182
2183
2184class RemoteWithConnectionModelProperty(Property):
2185    arg_types = {"this": True}
2186
2187
2188class ReturnsProperty(Property):
2189    arg_types = {"this": True, "is_table": False, "table": False}
2190
2191
2192class RowFormatProperty(Property):
2193    arg_types = {"this": True}
2194
2195
2196class RowFormatDelimitedProperty(Property):
2197    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2198    arg_types = {
2199        "fields": False,
2200        "escaped": False,
2201        "collection_items": False,
2202        "map_keys": False,
2203        "lines": False,
2204        "null": False,
2205        "serde": False,
2206    }
2207
2208
2209class RowFormatSerdeProperty(Property):
2210    arg_types = {"this": True, "serde_properties": False}
2211
2212
2213# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2214class QueryTransform(Expression):
2215    arg_types = {
2216        "expressions": True,
2217        "command_script": True,
2218        "schema": False,
2219        "row_format_before": False,
2220        "record_writer": False,
2221        "row_format_after": False,
2222        "record_reader": False,
2223    }
2224
2225
2226class SampleProperty(Property):
2227    arg_types = {"this": True}
2228
2229
2230class SchemaCommentProperty(Property):
2231    arg_types = {"this": True}
2232
2233
2234class SerdeProperties(Property):
2235    arg_types = {"expressions": True}
2236
2237
2238class SetProperty(Property):
2239    arg_types = {"multi": True}
2240
2241
2242class SettingsProperty(Property):
2243    arg_types = {"expressions": True}
2244
2245
2246class SortKeyProperty(Property):
2247    arg_types = {"this": True, "compound": False}
2248
2249
2250class SqlSecurityProperty(Property):
2251    arg_types = {"definer": True}
2252
2253
2254class StabilityProperty(Property):
2255    arg_types = {"this": True}
2256
2257
2258class TemporaryProperty(Property):
2259    arg_types = {}
2260
2261
2262class TransformModelProperty(Property):
2263    arg_types = {"expressions": True}
2264
2265
2266class TransientProperty(Property):
2267    arg_types = {"this": False}
2268
2269
2270class VolatileProperty(Property):
2271    arg_types = {"this": False}
2272
2273
2274class WithDataProperty(Property):
2275    arg_types = {"no": True, "statistics": False}
2276
2277
2278class WithJournalTableProperty(Property):
2279    arg_types = {"this": True}
2280
2281
2282class WithSystemVersioningProperty(Property):
2283    # this -> history table name, expression -> data consistency check
2284    arg_types = {"this": False, "expression": False}
2285
2286
2287class Properties(Expression):
2288    arg_types = {"expressions": True}
2289
2290    NAME_TO_PROPERTY = {
2291        "ALGORITHM": AlgorithmProperty,
2292        "AUTO_INCREMENT": AutoIncrementProperty,
2293        "CHARACTER SET": CharacterSetProperty,
2294        "CLUSTERED_BY": ClusteredByProperty,
2295        "COLLATE": CollateProperty,
2296        "COMMENT": SchemaCommentProperty,
2297        "DEFINER": DefinerProperty,
2298        "DISTKEY": DistKeyProperty,
2299        "DISTSTYLE": DistStyleProperty,
2300        "ENGINE": EngineProperty,
2301        "EXECUTE AS": ExecuteAsProperty,
2302        "FORMAT": FileFormatProperty,
2303        "LANGUAGE": LanguageProperty,
2304        "LOCATION": LocationProperty,
2305        "PARTITIONED_BY": PartitionedByProperty,
2306        "RETURNS": ReturnsProperty,
2307        "ROW_FORMAT": RowFormatProperty,
2308        "SORTKEY": SortKeyProperty,
2309    }
2310
2311    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2312
2313    # CREATE property locations
2314    # Form: schema specified
2315    #   create [POST_CREATE]
2316    #     table a [POST_NAME]
2317    #     (b int) [POST_SCHEMA]
2318    #     with ([POST_WITH])
2319    #     index (b) [POST_INDEX]
2320    #
2321    # Form: alias selection
2322    #   create [POST_CREATE]
2323    #     table a [POST_NAME]
2324    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2325    #     index (c) [POST_INDEX]
2326    class Location(AutoName):
2327        POST_CREATE = auto()
2328        POST_NAME = auto()
2329        POST_SCHEMA = auto()
2330        POST_WITH = auto()
2331        POST_ALIAS = auto()
2332        POST_EXPRESSION = auto()
2333        POST_INDEX = auto()
2334        UNSUPPORTED = auto()
2335
2336    @classmethod
2337    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2338        expressions = []
2339        for key, value in properties_dict.items():
2340            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2341            if property_cls:
2342                expressions.append(property_cls(this=convert(value)))
2343            else:
2344                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2345
2346        return cls(expressions=expressions)
2347
2348
2349class Qualify(Expression):
2350    pass
2351
2352
2353class InputOutputFormat(Expression):
2354    arg_types = {"input_format": False, "output_format": False}
2355
2356
2357# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2358class Return(Expression):
2359    pass
2360
2361
2362class Reference(Expression):
2363    arg_types = {"this": True, "expressions": False, "options": False}
2364
2365
2366class Tuple(Expression):
2367    arg_types = {"expressions": False}
2368
2369    def isin(
2370        self,
2371        *expressions: t.Any,
2372        query: t.Optional[ExpOrStr] = None,
2373        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2374        copy: bool = True,
2375        **opts,
2376    ) -> In:
2377        return In(
2378            this=maybe_copy(self, copy),
2379            expressions=[convert(e, copy=copy) for e in expressions],
2380            query=maybe_parse(query, copy=copy, **opts) if query else None,
2381            unnest=Unnest(
2382                expressions=[
2383                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2384                ]
2385            )
2386            if unnest
2387            else None,
2388        )
2389
2390
2391class Subqueryable(Unionable):
2392    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2393        """
2394        Convert this expression to an aliased expression that can be used as a Subquery.
2395
2396        Example:
2397            >>> subquery = Select().select("x").from_("tbl").subquery()
2398            >>> Select().select("x").from_(subquery).sql()
2399            'SELECT x FROM (SELECT x FROM tbl)'
2400
2401        Args:
2402            alias (str | Identifier): an optional alias for the subquery
2403            copy (bool): if `False`, modify this expression instance in-place.
2404
2405        Returns:
2406            Alias: the subquery
2407        """
2408        instance = maybe_copy(self, copy)
2409        if not isinstance(alias, Expression):
2410            alias = TableAlias(this=to_identifier(alias)) if alias else None
2411
2412        return Subquery(this=instance, alias=alias)
2413
2414    def limit(
2415        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2416    ) -> Select:
2417        raise NotImplementedError
2418
2419    @property
2420    def ctes(self):
2421        with_ = self.args.get("with")
2422        if not with_:
2423            return []
2424        return with_.expressions
2425
2426    @property
2427    def selects(self) -> t.List[Expression]:
2428        raise NotImplementedError("Subqueryable objects must implement `selects`")
2429
2430    @property
2431    def named_selects(self) -> t.List[str]:
2432        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2433
2434    def select(
2435        self,
2436        *expressions: t.Optional[ExpOrStr],
2437        append: bool = True,
2438        dialect: DialectType = None,
2439        copy: bool = True,
2440        **opts,
2441    ) -> Subqueryable:
2442        raise NotImplementedError("Subqueryable objects must implement `select`")
2443
2444    def with_(
2445        self,
2446        alias: ExpOrStr,
2447        as_: ExpOrStr,
2448        recursive: t.Optional[bool] = None,
2449        append: bool = True,
2450        dialect: DialectType = None,
2451        copy: bool = True,
2452        **opts,
2453    ) -> Subqueryable:
2454        """
2455        Append to or set the common table expressions.
2456
2457        Example:
2458            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2459            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2460
2461        Args:
2462            alias: the SQL code string to parse as the table name.
2463                If an `Expression` instance is passed, this is used as-is.
2464            as_: the SQL code string to parse as the table expression.
2465                If an `Expression` instance is passed, it will be used as-is.
2466            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2467            append: if `True`, add to any existing expressions.
2468                Otherwise, this resets the expressions.
2469            dialect: the dialect used to parse the input expression.
2470            copy: if `False`, modify this expression instance in-place.
2471            opts: other options to use to parse the input expressions.
2472
2473        Returns:
2474            The modified expression.
2475        """
2476        return _apply_cte_builder(
2477            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2478        )
2479
2480
2481QUERY_MODIFIERS = {
2482    "match": False,
2483    "laterals": False,
2484    "joins": False,
2485    "connect": False,
2486    "pivots": False,
2487    "where": False,
2488    "group": False,
2489    "having": False,
2490    "qualify": False,
2491    "windows": False,
2492    "distribute": False,
2493    "sort": False,
2494    "cluster": False,
2495    "order": False,
2496    "limit": False,
2497    "offset": False,
2498    "locks": False,
2499    "sample": False,
2500    "settings": False,
2501    "format": False,
2502}
2503
2504
2505# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2506class WithTableHint(Expression):
2507    arg_types = {"expressions": True}
2508
2509
2510# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2511class IndexTableHint(Expression):
2512    arg_types = {"this": True, "expressions": False, "target": False}
2513
2514
2515class Table(Expression):
2516    arg_types = {
2517        "this": True,
2518        "alias": False,
2519        "db": False,
2520        "catalog": False,
2521        "laterals": False,
2522        "joins": False,
2523        "pivots": False,
2524        "hints": False,
2525        "system_time": False,
2526        "version": False,
2527        "format": False,
2528        "pattern": False,
2529        "index": False,
2530        "ordinality": False,
2531    }
2532
2533    @property
2534    def name(self) -> str:
2535        if isinstance(self.this, Func):
2536            return ""
2537        return self.this.name
2538
2539    @property
2540    def db(self) -> str:
2541        return self.text("db")
2542
2543    @property
2544    def catalog(self) -> str:
2545        return self.text("catalog")
2546
2547    @property
2548    def selects(self) -> t.List[Expression]:
2549        return []
2550
2551    @property
2552    def named_selects(self) -> t.List[str]:
2553        return []
2554
2555    @property
2556    def parts(self) -> t.List[Expression]:
2557        """Return the parts of a table in order catalog, db, table."""
2558        parts: t.List[Expression] = []
2559
2560        for arg in ("catalog", "db", "this"):
2561            part = self.args.get(arg)
2562
2563            if isinstance(part, Dot):
2564                parts.extend(part.flatten())
2565            elif isinstance(part, Expression):
2566                parts.append(part)
2567
2568        return parts
2569
2570
2571class Union(Subqueryable):
2572    arg_types = {
2573        "with": False,
2574        "this": True,
2575        "expression": True,
2576        "distinct": False,
2577        "by_name": False,
2578        **QUERY_MODIFIERS,
2579    }
2580
2581    def limit(
2582        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2583    ) -> Select:
2584        """
2585        Set the LIMIT expression.
2586
2587        Example:
2588            >>> select("1").union(select("1")).limit(1).sql()
2589            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2590
2591        Args:
2592            expression: the SQL code string to parse.
2593                This can also be an integer.
2594                If a `Limit` instance is passed, this is used as-is.
2595                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2596            dialect: the dialect used to parse the input expression.
2597            copy: if `False`, modify this expression instance in-place.
2598            opts: other options to use to parse the input expressions.
2599
2600        Returns:
2601            The limited subqueryable.
2602        """
2603        return (
2604            select("*")
2605            .from_(self.subquery(alias="_l_0", copy=copy))
2606            .limit(expression, dialect=dialect, copy=False, **opts)
2607        )
2608
2609    def select(
2610        self,
2611        *expressions: t.Optional[ExpOrStr],
2612        append: bool = True,
2613        dialect: DialectType = None,
2614        copy: bool = True,
2615        **opts,
2616    ) -> Union:
2617        """Append to or set the SELECT of the union recursively.
2618
2619        Example:
2620            >>> from sqlglot import parse_one
2621            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2622            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2623
2624        Args:
2625            *expressions: the SQL code strings to parse.
2626                If an `Expression` instance is passed, it will be used as-is.
2627            append: if `True`, add to any existing expressions.
2628                Otherwise, this resets the expressions.
2629            dialect: the dialect used to parse the input expressions.
2630            copy: if `False`, modify this expression instance in-place.
2631            opts: other options to use to parse the input expressions.
2632
2633        Returns:
2634            Union: the modified expression.
2635        """
2636        this = self.copy() if copy else self
2637        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2638        this.expression.unnest().select(
2639            *expressions, append=append, dialect=dialect, copy=False, **opts
2640        )
2641        return this
2642
2643    @property
2644    def named_selects(self) -> t.List[str]:
2645        return self.this.unnest().named_selects
2646
2647    @property
2648    def is_star(self) -> bool:
2649        return self.this.is_star or self.expression.is_star
2650
2651    @property
2652    def selects(self) -> t.List[Expression]:
2653        return self.this.unnest().selects
2654
2655    @property
2656    def left(self) -> Expression:
2657        return self.this
2658
2659    @property
2660    def right(self) -> Expression:
2661        return self.expression
2662
2663
2664class Except(Union):
2665    pass
2666
2667
2668class Intersect(Union):
2669    pass
2670
2671
2672class Unnest(UDTF):
2673    arg_types = {
2674        "expressions": True,
2675        "alias": False,
2676        "offset": False,
2677    }
2678
2679
2680class Update(Expression):
2681    arg_types = {
2682        "with": False,
2683        "this": False,
2684        "expressions": True,
2685        "from": False,
2686        "where": False,
2687        "returning": False,
2688        "order": False,
2689        "limit": False,
2690    }
2691
2692
2693class Values(UDTF):
2694    arg_types = {"expressions": True, "alias": False}
2695
2696
2697class Var(Expression):
2698    pass
2699
2700
2701class Version(Expression):
2702    """
2703    Time travel, iceberg, bigquery etc
2704    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2705    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2706    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2707    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2708    this is either TIMESTAMP or VERSION
2709    kind is ("AS OF", "BETWEEN")
2710    """
2711
2712    arg_types = {"this": True, "kind": True, "expression": False}
2713
2714
2715class Schema(Expression):
2716    arg_types = {"this": False, "expressions": False}
2717
2718
2719# https://dev.mysql.com/doc/refman/8.0/en/select.html
2720# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2721class Lock(Expression):
2722    arg_types = {"update": True, "expressions": False, "wait": False}
2723
2724
2725class Select(Subqueryable):
2726    arg_types = {
2727        "with": False,
2728        "kind": False,
2729        "expressions": False,
2730        "hint": False,
2731        "distinct": False,
2732        "into": False,
2733        "from": False,
2734        **QUERY_MODIFIERS,
2735    }
2736
2737    def from_(
2738        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2739    ) -> Select:
2740        """
2741        Set the FROM expression.
2742
2743        Example:
2744            >>> Select().from_("tbl").select("x").sql()
2745            'SELECT x FROM tbl'
2746
2747        Args:
2748            expression : the SQL code strings to parse.
2749                If a `From` instance is passed, this is used as-is.
2750                If another `Expression` instance is passed, it will be wrapped in a `From`.
2751            dialect: the dialect used to parse the input expression.
2752            copy: if `False`, modify this expression instance in-place.
2753            opts: other options to use to parse the input expressions.
2754
2755        Returns:
2756            The modified Select expression.
2757        """
2758        return _apply_builder(
2759            expression=expression,
2760            instance=self,
2761            arg="from",
2762            into=From,
2763            prefix="FROM",
2764            dialect=dialect,
2765            copy=copy,
2766            **opts,
2767        )
2768
2769    def group_by(
2770        self,
2771        *expressions: t.Optional[ExpOrStr],
2772        append: bool = True,
2773        dialect: DialectType = None,
2774        copy: bool = True,
2775        **opts,
2776    ) -> Select:
2777        """
2778        Set the GROUP BY expression.
2779
2780        Example:
2781            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2782            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2783
2784        Args:
2785            *expressions: the SQL code strings to parse.
2786                If a `Group` instance is passed, this is used as-is.
2787                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2788                If nothing is passed in then a group by is not applied to the expression
2789            append: if `True`, add to any existing expressions.
2790                Otherwise, this flattens all the `Group` expression into a single expression.
2791            dialect: the dialect used to parse the input expression.
2792            copy: if `False`, modify this expression instance in-place.
2793            opts: other options to use to parse the input expressions.
2794
2795        Returns:
2796            The modified Select expression.
2797        """
2798        if not expressions:
2799            return self if not copy else self.copy()
2800
2801        return _apply_child_list_builder(
2802            *expressions,
2803            instance=self,
2804            arg="group",
2805            append=append,
2806            copy=copy,
2807            prefix="GROUP BY",
2808            into=Group,
2809            dialect=dialect,
2810            **opts,
2811        )
2812
2813    def order_by(
2814        self,
2815        *expressions: t.Optional[ExpOrStr],
2816        append: bool = True,
2817        dialect: DialectType = None,
2818        copy: bool = True,
2819        **opts,
2820    ) -> Select:
2821        """
2822        Set the ORDER BY expression.
2823
2824        Example:
2825            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2826            'SELECT x FROM tbl ORDER BY x DESC'
2827
2828        Args:
2829            *expressions: the SQL code strings to parse.
2830                If a `Group` instance is passed, this is used as-is.
2831                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2832            append: if `True`, add to any existing expressions.
2833                Otherwise, this flattens all the `Order` expression into a single expression.
2834            dialect: the dialect used to parse the input expression.
2835            copy: if `False`, modify this expression instance in-place.
2836            opts: other options to use to parse the input expressions.
2837
2838        Returns:
2839            The modified Select expression.
2840        """
2841        return _apply_child_list_builder(
2842            *expressions,
2843            instance=self,
2844            arg="order",
2845            append=append,
2846            copy=copy,
2847            prefix="ORDER BY",
2848            into=Order,
2849            dialect=dialect,
2850            **opts,
2851        )
2852
2853    def sort_by(
2854        self,
2855        *expressions: t.Optional[ExpOrStr],
2856        append: bool = True,
2857        dialect: DialectType = None,
2858        copy: bool = True,
2859        **opts,
2860    ) -> Select:
2861        """
2862        Set the SORT BY expression.
2863
2864        Example:
2865            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2866            'SELECT x FROM tbl SORT BY x DESC'
2867
2868        Args:
2869            *expressions: the SQL code strings to parse.
2870                If a `Group` instance is passed, this is used as-is.
2871                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2872            append: if `True`, add to any existing expressions.
2873                Otherwise, this flattens all the `Order` expression into a single expression.
2874            dialect: the dialect used to parse the input expression.
2875            copy: if `False`, modify this expression instance in-place.
2876            opts: other options to use to parse the input expressions.
2877
2878        Returns:
2879            The modified Select expression.
2880        """
2881        return _apply_child_list_builder(
2882            *expressions,
2883            instance=self,
2884            arg="sort",
2885            append=append,
2886            copy=copy,
2887            prefix="SORT BY",
2888            into=Sort,
2889            dialect=dialect,
2890            **opts,
2891        )
2892
2893    def cluster_by(
2894        self,
2895        *expressions: t.Optional[ExpOrStr],
2896        append: bool = True,
2897        dialect: DialectType = None,
2898        copy: bool = True,
2899        **opts,
2900    ) -> Select:
2901        """
2902        Set the CLUSTER BY expression.
2903
2904        Example:
2905            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2906            'SELECT x FROM tbl CLUSTER BY x DESC'
2907
2908        Args:
2909            *expressions: the SQL code strings to parse.
2910                If a `Group` instance is passed, this is used as-is.
2911                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2912            append: if `True`, add to any existing expressions.
2913                Otherwise, this flattens all the `Order` expression into a single expression.
2914            dialect: the dialect used to parse the input expression.
2915            copy: if `False`, modify this expression instance in-place.
2916            opts: other options to use to parse the input expressions.
2917
2918        Returns:
2919            The modified Select expression.
2920        """
2921        return _apply_child_list_builder(
2922            *expressions,
2923            instance=self,
2924            arg="cluster",
2925            append=append,
2926            copy=copy,
2927            prefix="CLUSTER BY",
2928            into=Cluster,
2929            dialect=dialect,
2930            **opts,
2931        )
2932
2933    def limit(
2934        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2935    ) -> Select:
2936        """
2937        Set the LIMIT expression.
2938
2939        Example:
2940            >>> Select().from_("tbl").select("x").limit(10).sql()
2941            'SELECT x FROM tbl LIMIT 10'
2942
2943        Args:
2944            expression: the SQL code string to parse.
2945                This can also be an integer.
2946                If a `Limit` instance is passed, this is used as-is.
2947                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2948            dialect: the dialect used to parse the input expression.
2949            copy: if `False`, modify this expression instance in-place.
2950            opts: other options to use to parse the input expressions.
2951
2952        Returns:
2953            Select: the modified expression.
2954        """
2955        return _apply_builder(
2956            expression=expression,
2957            instance=self,
2958            arg="limit",
2959            into=Limit,
2960            prefix="LIMIT",
2961            dialect=dialect,
2962            copy=copy,
2963            into_arg="expression",
2964            **opts,
2965        )
2966
2967    def offset(
2968        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2969    ) -> Select:
2970        """
2971        Set the OFFSET expression.
2972
2973        Example:
2974            >>> Select().from_("tbl").select("x").offset(10).sql()
2975            'SELECT x FROM tbl OFFSET 10'
2976
2977        Args:
2978            expression: the SQL code string to parse.
2979                This can also be an integer.
2980                If a `Offset` instance is passed, this is used as-is.
2981                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2982            dialect: the dialect used to parse the input expression.
2983            copy: if `False`, modify this expression instance in-place.
2984            opts: other options to use to parse the input expressions.
2985
2986        Returns:
2987            The modified Select expression.
2988        """
2989        return _apply_builder(
2990            expression=expression,
2991            instance=self,
2992            arg="offset",
2993            into=Offset,
2994            prefix="OFFSET",
2995            dialect=dialect,
2996            copy=copy,
2997            into_arg="expression",
2998            **opts,
2999        )
3000
3001    def select(
3002        self,
3003        *expressions: t.Optional[ExpOrStr],
3004        append: bool = True,
3005        dialect: DialectType = None,
3006        copy: bool = True,
3007        **opts,
3008    ) -> Select:
3009        """
3010        Append to or set the SELECT expressions.
3011
3012        Example:
3013            >>> Select().select("x", "y").sql()
3014            'SELECT x, y'
3015
3016        Args:
3017            *expressions: the SQL code strings to parse.
3018                If an `Expression` instance is passed, it will be used as-is.
3019            append: if `True`, add to any existing expressions.
3020                Otherwise, this resets the expressions.
3021            dialect: the dialect used to parse the input expressions.
3022            copy: if `False`, modify this expression instance in-place.
3023            opts: other options to use to parse the input expressions.
3024
3025        Returns:
3026            The modified Select expression.
3027        """
3028        return _apply_list_builder(
3029            *expressions,
3030            instance=self,
3031            arg="expressions",
3032            append=append,
3033            dialect=dialect,
3034            copy=copy,
3035            **opts,
3036        )
3037
3038    def lateral(
3039        self,
3040        *expressions: t.Optional[ExpOrStr],
3041        append: bool = True,
3042        dialect: DialectType = None,
3043        copy: bool = True,
3044        **opts,
3045    ) -> Select:
3046        """
3047        Append to or set the LATERAL expressions.
3048
3049        Example:
3050            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3051            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3052
3053        Args:
3054            *expressions: the SQL code strings to parse.
3055                If an `Expression` instance is passed, it will be used as-is.
3056            append: if `True`, add to any existing expressions.
3057                Otherwise, this resets the expressions.
3058            dialect: the dialect used to parse the input expressions.
3059            copy: if `False`, modify this expression instance in-place.
3060            opts: other options to use to parse the input expressions.
3061
3062        Returns:
3063            The modified Select expression.
3064        """
3065        return _apply_list_builder(
3066            *expressions,
3067            instance=self,
3068            arg="laterals",
3069            append=append,
3070            into=Lateral,
3071            prefix="LATERAL VIEW",
3072            dialect=dialect,
3073            copy=copy,
3074            **opts,
3075        )
3076
3077    def join(
3078        self,
3079        expression: ExpOrStr,
3080        on: t.Optional[ExpOrStr] = None,
3081        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3082        append: bool = True,
3083        join_type: t.Optional[str] = None,
3084        join_alias: t.Optional[Identifier | str] = None,
3085        dialect: DialectType = None,
3086        copy: bool = True,
3087        **opts,
3088    ) -> Select:
3089        """
3090        Append to or set the JOIN expressions.
3091
3092        Example:
3093            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3094            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3095
3096            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3097            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3098
3099            Use `join_type` to change the type of join:
3100
3101            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3102            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3103
3104        Args:
3105            expression: the SQL code string to parse.
3106                If an `Expression` instance is passed, it will be used as-is.
3107            on: optionally specify the join "on" criteria as a SQL string.
3108                If an `Expression` instance is passed, it will be used as-is.
3109            using: optionally specify the join "using" criteria as a SQL string.
3110                If an `Expression` instance is passed, it will be used as-is.
3111            append: if `True`, add to any existing expressions.
3112                Otherwise, this resets the expressions.
3113            join_type: if set, alter the parsed join type.
3114            join_alias: an optional alias for the joined source.
3115            dialect: the dialect used to parse the input expressions.
3116            copy: if `False`, modify this expression instance in-place.
3117            opts: other options to use to parse the input expressions.
3118
3119        Returns:
3120            Select: the modified expression.
3121        """
3122        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3123
3124        try:
3125            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3126        except ParseError:
3127            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3128
3129        join = expression if isinstance(expression, Join) else Join(this=expression)
3130
3131        if isinstance(join.this, Select):
3132            join.this.replace(join.this.subquery())
3133
3134        if join_type:
3135            method: t.Optional[Token]
3136            side: t.Optional[Token]
3137            kind: t.Optional[Token]
3138
3139            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3140
3141            if method:
3142                join.set("method", method.text)
3143            if side:
3144                join.set("side", side.text)
3145            if kind:
3146                join.set("kind", kind.text)
3147
3148        if on:
3149            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3150            join.set("on", on)
3151
3152        if using:
3153            join = _apply_list_builder(
3154                *ensure_list(using),
3155                instance=join,
3156                arg="using",
3157                append=append,
3158                copy=copy,
3159                into=Identifier,
3160                **opts,
3161            )
3162
3163        if join_alias:
3164            join.set("this", alias_(join.this, join_alias, table=True))
3165
3166        return _apply_list_builder(
3167            join,
3168            instance=self,
3169            arg="joins",
3170            append=append,
3171            copy=copy,
3172            **opts,
3173        )
3174
3175    def where(
3176        self,
3177        *expressions: t.Optional[ExpOrStr],
3178        append: bool = True,
3179        dialect: DialectType = None,
3180        copy: bool = True,
3181        **opts,
3182    ) -> Select:
3183        """
3184        Append to or set the WHERE expressions.
3185
3186        Example:
3187            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3188            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3189
3190        Args:
3191            *expressions: the SQL code strings to parse.
3192                If an `Expression` instance is passed, it will be used as-is.
3193                Multiple expressions are combined with an AND operator.
3194            append: if `True`, AND the new expressions to any existing expression.
3195                Otherwise, this resets the expression.
3196            dialect: the dialect used to parse the input expressions.
3197            copy: if `False`, modify this expression instance in-place.
3198            opts: other options to use to parse the input expressions.
3199
3200        Returns:
3201            Select: the modified expression.
3202        """
3203        return _apply_conjunction_builder(
3204            *expressions,
3205            instance=self,
3206            arg="where",
3207            append=append,
3208            into=Where,
3209            dialect=dialect,
3210            copy=copy,
3211            **opts,
3212        )
3213
3214    def having(
3215        self,
3216        *expressions: t.Optional[ExpOrStr],
3217        append: bool = True,
3218        dialect: DialectType = None,
3219        copy: bool = True,
3220        **opts,
3221    ) -> Select:
3222        """
3223        Append to or set the HAVING expressions.
3224
3225        Example:
3226            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3227            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3228
3229        Args:
3230            *expressions: the SQL code strings to parse.
3231                If an `Expression` instance is passed, it will be used as-is.
3232                Multiple expressions are combined with an AND operator.
3233            append: if `True`, AND the new expressions to any existing expression.
3234                Otherwise, this resets the expression.
3235            dialect: the dialect used to parse the input expressions.
3236            copy: if `False`, modify this expression instance in-place.
3237            opts: other options to use to parse the input expressions.
3238
3239        Returns:
3240            The modified Select expression.
3241        """
3242        return _apply_conjunction_builder(
3243            *expressions,
3244            instance=self,
3245            arg="having",
3246            append=append,
3247            into=Having,
3248            dialect=dialect,
3249            copy=copy,
3250            **opts,
3251        )
3252
3253    def window(
3254        self,
3255        *expressions: t.Optional[ExpOrStr],
3256        append: bool = True,
3257        dialect: DialectType = None,
3258        copy: bool = True,
3259        **opts,
3260    ) -> Select:
3261        return _apply_list_builder(
3262            *expressions,
3263            instance=self,
3264            arg="windows",
3265            append=append,
3266            into=Window,
3267            dialect=dialect,
3268            copy=copy,
3269            **opts,
3270        )
3271
3272    def qualify(
3273        self,
3274        *expressions: t.Optional[ExpOrStr],
3275        append: bool = True,
3276        dialect: DialectType = None,
3277        copy: bool = True,
3278        **opts,
3279    ) -> Select:
3280        return _apply_conjunction_builder(
3281            *expressions,
3282            instance=self,
3283            arg="qualify",
3284            append=append,
3285            into=Qualify,
3286            dialect=dialect,
3287            copy=copy,
3288            **opts,
3289        )
3290
3291    def distinct(
3292        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3293    ) -> Select:
3294        """
3295        Set the OFFSET expression.
3296
3297        Example:
3298            >>> Select().from_("tbl").select("x").distinct().sql()
3299            'SELECT DISTINCT x FROM tbl'
3300
3301        Args:
3302            ons: the expressions to distinct on
3303            distinct: whether the Select should be distinct
3304            copy: if `False`, modify this expression instance in-place.
3305
3306        Returns:
3307            Select: the modified expression.
3308        """
3309        instance = maybe_copy(self, copy)
3310        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3311        instance.set("distinct", Distinct(on=on) if distinct else None)
3312        return instance
3313
3314    def ctas(
3315        self,
3316        table: ExpOrStr,
3317        properties: t.Optional[t.Dict] = None,
3318        dialect: DialectType = None,
3319        copy: bool = True,
3320        **opts,
3321    ) -> Create:
3322        """
3323        Convert this expression to a CREATE TABLE AS statement.
3324
3325        Example:
3326            >>> Select().select("*").from_("tbl").ctas("x").sql()
3327            'CREATE TABLE x AS SELECT * FROM tbl'
3328
3329        Args:
3330            table: the SQL code string to parse as the table name.
3331                If another `Expression` instance is passed, it will be used as-is.
3332            properties: an optional mapping of table properties
3333            dialect: the dialect used to parse the input table.
3334            copy: if `False`, modify this expression instance in-place.
3335            opts: other options to use to parse the input table.
3336
3337        Returns:
3338            The new Create expression.
3339        """
3340        instance = maybe_copy(self, copy)
3341        table_expression = maybe_parse(
3342            table,
3343            into=Table,
3344            dialect=dialect,
3345            **opts,
3346        )
3347        properties_expression = None
3348        if properties:
3349            properties_expression = Properties.from_dict(properties)
3350
3351        return Create(
3352            this=table_expression,
3353            kind="table",
3354            expression=instance,
3355            properties=properties_expression,
3356        )
3357
3358    def lock(self, update: bool = True, copy: bool = True) -> Select:
3359        """
3360        Set the locking read mode for this expression.
3361
3362        Examples:
3363            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3364            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3365
3366            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3367            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3368
3369        Args:
3370            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3371            copy: if `False`, modify this expression instance in-place.
3372
3373        Returns:
3374            The modified expression.
3375        """
3376        inst = maybe_copy(self, copy)
3377        inst.set("locks", [Lock(update=update)])
3378
3379        return inst
3380
3381    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3382        """
3383        Set hints for this expression.
3384
3385        Examples:
3386            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3387            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3388
3389        Args:
3390            hints: The SQL code strings to parse as the hints.
3391                If an `Expression` instance is passed, it will be used as-is.
3392            dialect: The dialect used to parse the hints.
3393            copy: If `False`, modify this expression instance in-place.
3394
3395        Returns:
3396            The modified expression.
3397        """
3398        inst = maybe_copy(self, copy)
3399        inst.set(
3400            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3401        )
3402
3403        return inst
3404
3405    @property
3406    def named_selects(self) -> t.List[str]:
3407        return [e.output_name for e in self.expressions if e.alias_or_name]
3408
3409    @property
3410    def is_star(self) -> bool:
3411        return any(expression.is_star for expression in self.expressions)
3412
3413    @property
3414    def selects(self) -> t.List[Expression]:
3415        return self.expressions
3416
3417
3418class Subquery(DerivedTable, Unionable):
3419    arg_types = {
3420        "this": True,
3421        "alias": False,
3422        "with": False,
3423        **QUERY_MODIFIERS,
3424    }
3425
3426    def unnest(self):
3427        """
3428        Returns the first non subquery.
3429        """
3430        expression = self
3431        while isinstance(expression, Subquery):
3432            expression = expression.this
3433        return expression
3434
3435    def unwrap(self) -> Subquery:
3436        expression = self
3437        while expression.same_parent and expression.is_wrapper:
3438            expression = t.cast(Subquery, expression.parent)
3439        return expression
3440
3441    @property
3442    def is_wrapper(self) -> bool:
3443        """
3444        Whether this Subquery acts as a simple wrapper around another expression.
3445
3446        SELECT * FROM (((SELECT * FROM t)))
3447                      ^
3448                      This corresponds to a "wrapper" Subquery node
3449        """
3450        return all(v is None for k, v in self.args.items() if k != "this")
3451
3452    @property
3453    def is_star(self) -> bool:
3454        return self.this.is_star
3455
3456    @property
3457    def output_name(self) -> str:
3458        return self.alias
3459
3460
3461class TableSample(Expression):
3462    arg_types = {
3463        "this": False,
3464        "expressions": False,
3465        "method": False,
3466        "bucket_numerator": False,
3467        "bucket_denominator": False,
3468        "bucket_field": False,
3469        "percent": False,
3470        "rows": False,
3471        "size": False,
3472        "seed": False,
3473        "kind": False,
3474    }
3475
3476
3477class Tag(Expression):
3478    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3479
3480    arg_types = {
3481        "this": False,
3482        "prefix": False,
3483        "postfix": False,
3484    }
3485
3486
3487# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3488# https://duckdb.org/docs/sql/statements/pivot
3489class Pivot(Expression):
3490    arg_types = {
3491        "this": False,
3492        "alias": False,
3493        "expressions": False,
3494        "field": False,
3495        "unpivot": False,
3496        "using": False,
3497        "group": False,
3498        "columns": False,
3499        "include_nulls": False,
3500    }
3501
3502
3503class Window(Condition):
3504    arg_types = {
3505        "this": True,
3506        "partition_by": False,
3507        "order": False,
3508        "spec": False,
3509        "alias": False,
3510        "over": False,
3511        "first": False,
3512    }
3513
3514
3515class WindowSpec(Expression):
3516    arg_types = {
3517        "kind": False,
3518        "start": False,
3519        "start_side": False,
3520        "end": False,
3521        "end_side": False,
3522    }
3523
3524
3525class Where(Expression):
3526    pass
3527
3528
3529class Star(Expression):
3530    arg_types = {"except": False, "replace": False}
3531
3532    @property
3533    def name(self) -> str:
3534        return "*"
3535
3536    @property
3537    def output_name(self) -> str:
3538        return self.name
3539
3540
3541class Parameter(Condition):
3542    arg_types = {"this": True, "expression": False}
3543
3544
3545class SessionParameter(Condition):
3546    arg_types = {"this": True, "kind": False}
3547
3548
3549class Placeholder(Condition):
3550    arg_types = {"this": False, "kind": False}
3551
3552
3553class Null(Condition):
3554    arg_types: t.Dict[str, t.Any] = {}
3555
3556    @property
3557    def name(self) -> str:
3558        return "NULL"
3559
3560
3561class Boolean(Condition):
3562    pass
3563
3564
3565class DataTypeParam(Expression):
3566    arg_types = {"this": True, "expression": False}
3567
3568
3569class DataType(Expression):
3570    arg_types = {
3571        "this": True,
3572        "expressions": False,
3573        "nested": False,
3574        "values": False,
3575        "prefix": False,
3576        "kind": False,
3577    }
3578
3579    class Type(AutoName):
3580        ARRAY = auto()
3581        BIGDECIMAL = auto()
3582        BIGINT = auto()
3583        BIGSERIAL = auto()
3584        BINARY = auto()
3585        BIT = auto()
3586        BOOLEAN = auto()
3587        CHAR = auto()
3588        DATE = auto()
3589        DATEMULTIRANGE = auto()
3590        DATERANGE = auto()
3591        DATETIME = auto()
3592        DATETIME64 = auto()
3593        DECIMAL = auto()
3594        DOUBLE = auto()
3595        ENUM = auto()
3596        ENUM8 = auto()
3597        ENUM16 = auto()
3598        FIXEDSTRING = auto()
3599        FLOAT = auto()
3600        GEOGRAPHY = auto()
3601        GEOMETRY = auto()
3602        HLLSKETCH = auto()
3603        HSTORE = auto()
3604        IMAGE = auto()
3605        INET = auto()
3606        INT = auto()
3607        INT128 = auto()
3608        INT256 = auto()
3609        INT4MULTIRANGE = auto()
3610        INT4RANGE = auto()
3611        INT8MULTIRANGE = auto()
3612        INT8RANGE = auto()
3613        INTERVAL = auto()
3614        IPADDRESS = auto()
3615        IPPREFIX = auto()
3616        JSON = auto()
3617        JSONB = auto()
3618        LONGBLOB = auto()
3619        LONGTEXT = auto()
3620        LOWCARDINALITY = auto()
3621        MAP = auto()
3622        MEDIUMBLOB = auto()
3623        MEDIUMINT = auto()
3624        MEDIUMTEXT = auto()
3625        MONEY = auto()
3626        NCHAR = auto()
3627        NESTED = auto()
3628        NULL = auto()
3629        NULLABLE = auto()
3630        NUMMULTIRANGE = auto()
3631        NUMRANGE = auto()
3632        NVARCHAR = auto()
3633        OBJECT = auto()
3634        ROWVERSION = auto()
3635        SERIAL = auto()
3636        SET = auto()
3637        SMALLINT = auto()
3638        SMALLMONEY = auto()
3639        SMALLSERIAL = auto()
3640        STRUCT = auto()
3641        SUPER = auto()
3642        TEXT = auto()
3643        TINYBLOB = auto()
3644        TINYTEXT = auto()
3645        TIME = auto()
3646        TIMETZ = auto()
3647        TIMESTAMP = auto()
3648        TIMESTAMPLTZ = auto()
3649        TIMESTAMPTZ = auto()
3650        TIMESTAMP_S = auto()
3651        TIMESTAMP_MS = auto()
3652        TIMESTAMP_NS = auto()
3653        TINYINT = auto()
3654        TSMULTIRANGE = auto()
3655        TSRANGE = auto()
3656        TSTZMULTIRANGE = auto()
3657        TSTZRANGE = auto()
3658        UBIGINT = auto()
3659        UINT = auto()
3660        UINT128 = auto()
3661        UINT256 = auto()
3662        UMEDIUMINT = auto()
3663        UDECIMAL = auto()
3664        UNIQUEIDENTIFIER = auto()
3665        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3666        USERDEFINED = "USER-DEFINED"
3667        USMALLINT = auto()
3668        UTINYINT = auto()
3669        UUID = auto()
3670        VARBINARY = auto()
3671        VARCHAR = auto()
3672        VARIANT = auto()
3673        XML = auto()
3674        YEAR = auto()
3675
3676    TEXT_TYPES = {
3677        Type.CHAR,
3678        Type.NCHAR,
3679        Type.VARCHAR,
3680        Type.NVARCHAR,
3681        Type.TEXT,
3682    }
3683
3684    INTEGER_TYPES = {
3685        Type.INT,
3686        Type.TINYINT,
3687        Type.SMALLINT,
3688        Type.BIGINT,
3689        Type.INT128,
3690        Type.INT256,
3691        Type.BIT,
3692    }
3693
3694    FLOAT_TYPES = {
3695        Type.FLOAT,
3696        Type.DOUBLE,
3697    }
3698
3699    NUMERIC_TYPES = {
3700        *INTEGER_TYPES,
3701        *FLOAT_TYPES,
3702    }
3703
3704    TEMPORAL_TYPES = {
3705        Type.TIME,
3706        Type.TIMETZ,
3707        Type.TIMESTAMP,
3708        Type.TIMESTAMPTZ,
3709        Type.TIMESTAMPLTZ,
3710        Type.TIMESTAMP_S,
3711        Type.TIMESTAMP_MS,
3712        Type.TIMESTAMP_NS,
3713        Type.DATE,
3714        Type.DATETIME,
3715        Type.DATETIME64,
3716    }
3717
3718    @classmethod
3719    def build(
3720        cls,
3721        dtype: str | DataType | DataType.Type,
3722        dialect: DialectType = None,
3723        udt: bool = False,
3724        **kwargs,
3725    ) -> DataType:
3726        """
3727        Constructs a DataType object.
3728
3729        Args:
3730            dtype: the data type of interest.
3731            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3732            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3733                DataType, thus creating a user-defined type.
3734            kawrgs: additional arguments to pass in the constructor of DataType.
3735
3736        Returns:
3737            The constructed DataType object.
3738        """
3739        from sqlglot import parse_one
3740
3741        if isinstance(dtype, str):
3742            if dtype.upper() == "UNKNOWN":
3743                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3744
3745            try:
3746                data_type_exp = parse_one(
3747                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3748                )
3749            except ParseError:
3750                if udt:
3751                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3752                raise
3753        elif isinstance(dtype, DataType.Type):
3754            data_type_exp = DataType(this=dtype)
3755        elif isinstance(dtype, DataType):
3756            return dtype
3757        else:
3758            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3759
3760        return DataType(**{**data_type_exp.args, **kwargs})
3761
3762    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
3763        """
3764        Checks whether this DataType matches one of the provided data types. Nested types or precision
3765        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3766
3767        Args:
3768            dtypes: the data types to compare this DataType to.
3769
3770        Returns:
3771            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3772        """
3773        for dtype in dtypes:
3774            other = DataType.build(dtype, udt=True)
3775
3776            if (
3777                other.expressions
3778                or self.this == DataType.Type.USERDEFINED
3779                or other.this == DataType.Type.USERDEFINED
3780            ):
3781                matches = self == other
3782            else:
3783                matches = self.this == other.this
3784
3785            if matches:
3786                return True
3787        return False
3788
3789
3790# https://www.postgresql.org/docs/15/datatype-pseudo.html
3791class PseudoType(DataType):
3792    arg_types = {"this": True}
3793
3794
3795# https://www.postgresql.org/docs/15/datatype-oid.html
3796class ObjectIdentifier(DataType):
3797    arg_types = {"this": True}
3798
3799
3800# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3801class SubqueryPredicate(Predicate):
3802    pass
3803
3804
3805class All(SubqueryPredicate):
3806    pass
3807
3808
3809class Any(SubqueryPredicate):
3810    pass
3811
3812
3813class Exists(SubqueryPredicate):
3814    pass
3815
3816
3817# Commands to interact with the databases or engines. For most of the command
3818# expressions we parse whatever comes after the command's name as a string.
3819class Command(Expression):
3820    arg_types = {"this": True, "expression": False}
3821
3822
3823class Transaction(Expression):
3824    arg_types = {"this": False, "modes": False, "mark": False}
3825
3826
3827class Commit(Expression):
3828    arg_types = {"chain": False, "this": False, "durability": False}
3829
3830
3831class Rollback(Expression):
3832    arg_types = {"savepoint": False, "this": False}
3833
3834
3835class AlterTable(Expression):
3836    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
3837
3838
3839class AddConstraint(Expression):
3840    arg_types = {"this": False, "expression": False, "enforced": False}
3841
3842
3843class DropPartition(Expression):
3844    arg_types = {"expressions": True, "exists": False}
3845
3846
3847# Binary expressions like (ADD a b)
3848class Binary(Condition):
3849    arg_types = {"this": True, "expression": True}
3850
3851    @property
3852    def left(self) -> Expression:
3853        return self.this
3854
3855    @property
3856    def right(self) -> Expression:
3857        return self.expression
3858
3859
3860class Add(Binary):
3861    pass
3862
3863
3864class Connector(Binary):
3865    pass
3866
3867
3868class And(Connector):
3869    pass
3870
3871
3872class Or(Connector):
3873    pass
3874
3875
3876class BitwiseAnd(Binary):
3877    pass
3878
3879
3880class BitwiseLeftShift(Binary):
3881    pass
3882
3883
3884class BitwiseOr(Binary):
3885    pass
3886
3887
3888class BitwiseRightShift(Binary):
3889    pass
3890
3891
3892class BitwiseXor(Binary):
3893    pass
3894
3895
3896class Div(Binary):
3897    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
3898
3899
3900class Overlaps(Binary):
3901    pass
3902
3903
3904class Dot(Binary):
3905    @property
3906    def name(self) -> str:
3907        return self.expression.name
3908
3909    @property
3910    def output_name(self) -> str:
3911        return self.name
3912
3913    @classmethod
3914    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3915        """Build a Dot object with a sequence of expressions."""
3916        if len(expressions) < 2:
3917            raise ValueError(f"Dot requires >= 2 expressions.")
3918
3919        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
3920
3921
3922class DPipe(Binary):
3923    pass
3924
3925
3926class SafeDPipe(DPipe):
3927    pass
3928
3929
3930class EQ(Binary, Predicate):
3931    pass
3932
3933
3934class NullSafeEQ(Binary, Predicate):
3935    pass
3936
3937
3938class NullSafeNEQ(Binary, Predicate):
3939    pass
3940
3941
3942# Represents e.g. := in DuckDB which is mostly used for setting parameters
3943class PropertyEQ(Binary):
3944    pass
3945
3946
3947class Distance(Binary):
3948    pass
3949
3950
3951class Escape(Binary):
3952    pass
3953
3954
3955class Glob(Binary, Predicate):
3956    pass
3957
3958
3959class GT(Binary, Predicate):
3960    pass
3961
3962
3963class GTE(Binary, Predicate):
3964    pass
3965
3966
3967class ILike(Binary, Predicate):
3968    pass
3969
3970
3971class ILikeAny(Binary, Predicate):
3972    pass
3973
3974
3975class IntDiv(Binary):
3976    pass
3977
3978
3979class Is(Binary, Predicate):
3980    pass
3981
3982
3983class Kwarg(Binary):
3984    """Kwarg in special functions like func(kwarg => y)."""
3985
3986
3987class Like(Binary, Predicate):
3988    pass
3989
3990
3991class LikeAny(Binary, Predicate):
3992    pass
3993
3994
3995class LT(Binary, Predicate):
3996    pass
3997
3998
3999class LTE(Binary, Predicate):
4000    pass
4001
4002
4003class Mod(Binary):
4004    pass
4005
4006
4007class Mul(Binary):
4008    pass
4009
4010
4011class NEQ(Binary, Predicate):
4012    pass
4013
4014
4015class SimilarTo(Binary, Predicate):
4016    pass
4017
4018
4019class Slice(Binary):
4020    arg_types = {"this": False, "expression": False}
4021
4022
4023class Sub(Binary):
4024    pass
4025
4026
4027class ArrayOverlaps(Binary):
4028    pass
4029
4030
4031# Unary Expressions
4032# (NOT a)
4033class Unary(Condition):
4034    pass
4035
4036
4037class BitwiseNot(Unary):
4038    pass
4039
4040
4041class Not(Unary):
4042    pass
4043
4044
4045class Paren(Unary):
4046    arg_types = {"this": True, "with": False}
4047
4048    @property
4049    def output_name(self) -> str:
4050        return self.this.name
4051
4052
4053class Neg(Unary):
4054    pass
4055
4056
4057class Alias(Expression):
4058    arg_types = {"this": True, "alias": False}
4059
4060    @property
4061    def output_name(self) -> str:
4062        return self.alias
4063
4064
4065class Aliases(Expression):
4066    arg_types = {"this": True, "expressions": True}
4067
4068    @property
4069    def aliases(self):
4070        return self.expressions
4071
4072
4073class AtTimeZone(Expression):
4074    arg_types = {"this": True, "zone": True}
4075
4076
4077class Between(Predicate):
4078    arg_types = {"this": True, "low": True, "high": True}
4079
4080
4081class Bracket(Condition):
4082    arg_types = {"this": True, "expressions": True}
4083
4084    @property
4085    def output_name(self) -> str:
4086        if len(self.expressions) == 1:
4087            return self.expressions[0].output_name
4088
4089        return super().output_name
4090
4091
4092class SafeBracket(Bracket):
4093    """Represents array lookup where OOB index yields NULL instead of causing a failure."""
4094
4095
4096class Distinct(Expression):
4097    arg_types = {"expressions": False, "on": False}
4098
4099
4100class In(Predicate):
4101    arg_types = {
4102        "this": True,
4103        "expressions": False,
4104        "query": False,
4105        "unnest": False,
4106        "field": False,
4107        "is_global": False,
4108    }
4109
4110
4111# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4112class ForIn(Expression):
4113    arg_types = {"this": True, "expression": True}
4114
4115
4116class TimeUnit(Expression):
4117    """Automatically converts unit arg into a var."""
4118
4119    arg_types = {"unit": False}
4120
4121    UNABBREVIATED_UNIT_NAME = {
4122        "d": "day",
4123        "h": "hour",
4124        "m": "minute",
4125        "ms": "millisecond",
4126        "ns": "nanosecond",
4127        "q": "quarter",
4128        "s": "second",
4129        "us": "microsecond",
4130        "w": "week",
4131        "y": "year",
4132    }
4133
4134    VAR_LIKE = (Column, Literal, Var)
4135
4136    def __init__(self, **args):
4137        unit = args.get("unit")
4138        if isinstance(unit, self.VAR_LIKE):
4139            args["unit"] = Var(this=self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name)
4140        elif isinstance(unit, Week):
4141            unit.set("this", Var(this=unit.this.name))
4142
4143        super().__init__(**args)
4144
4145    @property
4146    def unit(self) -> t.Optional[Var]:
4147        return self.args.get("unit")
4148
4149
4150class IntervalOp(TimeUnit):
4151    arg_types = {"unit": True, "expression": True}
4152
4153    def interval(self):
4154        return Interval(
4155            this=self.expression.copy(),
4156            unit=self.unit.copy(),
4157        )
4158
4159
4160# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4161# https://trino.io/docs/current/language/types.html#interval-day-to-second
4162# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4163class IntervalSpan(DataType):
4164    arg_types = {"this": True, "expression": True}
4165
4166
4167class Interval(TimeUnit):
4168    arg_types = {"this": False, "unit": False}
4169
4170
4171class IgnoreNulls(Expression):
4172    pass
4173
4174
4175class RespectNulls(Expression):
4176    pass
4177
4178
4179# Functions
4180class Func(Condition):
4181    """
4182    The base class for all function expressions.
4183
4184    Attributes:
4185        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4186            treated as a variable length argument and the argument's value will be stored as a list.
4187        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
4188            for this function expression. These values are used to map this node to a name during parsing
4189            as well as to provide the function's name during SQL string generation. By default the SQL
4190            name is set to the expression's class name transformed to snake case.
4191    """
4192
4193    is_var_len_args = False
4194
4195    @classmethod
4196    def from_arg_list(cls, args):
4197        if cls.is_var_len_args:
4198            all_arg_keys = list(cls.arg_types)
4199            # If this function supports variable length argument treat the last argument as such.
4200            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4201            num_non_var = len(non_var_len_arg_keys)
4202
4203            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4204            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4205        else:
4206            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4207
4208        return cls(**args_dict)
4209
4210    @classmethod
4211    def sql_names(cls):
4212        if cls is Func:
4213            raise NotImplementedError(
4214                "SQL name is only supported by concrete function implementations"
4215            )
4216        if "_sql_names" not in cls.__dict__:
4217            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4218        return cls._sql_names
4219
4220    @classmethod
4221    def sql_name(cls):
4222        return cls.sql_names()[0]
4223
4224    @classmethod
4225    def default_parser_mappings(cls):
4226        return {name: cls.from_arg_list for name in cls.sql_names()}
4227
4228
4229class AggFunc(Func):
4230    pass
4231
4232
4233class ParameterizedAgg(AggFunc):
4234    arg_types = {"this": True, "expressions": True, "params": True}
4235
4236
4237class Abs(Func):
4238    pass
4239
4240
4241class ArgMax(AggFunc):
4242    arg_types = {"this": True, "expression": True, "count": False}
4243    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4244
4245
4246class ArgMin(AggFunc):
4247    arg_types = {"this": True, "expression": True, "count": False}
4248    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4249
4250
4251class ApproxTopK(AggFunc):
4252    arg_types = {"this": True, "expression": False, "counters": False}
4253
4254
4255class Flatten(Func):
4256    pass
4257
4258
4259# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4260class Transform(Func):
4261    arg_types = {"this": True, "expression": True}
4262
4263
4264class Anonymous(Func):
4265    arg_types = {"this": True, "expressions": False}
4266    is_var_len_args = True
4267
4268
4269# https://docs.snowflake.com/en/sql-reference/functions/hll
4270# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4271class Hll(AggFunc):
4272    arg_types = {"this": True, "expressions": False}
4273    is_var_len_args = True
4274
4275
4276class ApproxDistinct(AggFunc):
4277    arg_types = {"this": True, "accuracy": False}
4278    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4279
4280
4281class Array(Func):
4282    arg_types = {"expressions": False}
4283    is_var_len_args = True
4284
4285
4286# https://docs.snowflake.com/en/sql-reference/functions/to_char
4287class ToChar(Func):
4288    arg_types = {"this": True, "format": False}
4289
4290
4291class GenerateSeries(Func):
4292    arg_types = {"start": True, "end": True, "step": False}
4293
4294
4295class ArrayAgg(AggFunc):
4296    pass
4297
4298
4299class ArrayAll(Func):
4300    arg_types = {"this": True, "expression": True}
4301
4302
4303class ArrayAny(Func):
4304    arg_types = {"this": True, "expression": True}
4305
4306
4307class ArrayConcat(Func):
4308    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4309    arg_types = {"this": True, "expressions": False}
4310    is_var_len_args = True
4311
4312
4313class ArrayContains(Binary, Func):
4314    pass
4315
4316
4317class ArrayContained(Binary):
4318    pass
4319
4320
4321class ArrayFilter(Func):
4322    arg_types = {"this": True, "expression": True}
4323    _sql_names = ["FILTER", "ARRAY_FILTER"]
4324
4325
4326class ArrayJoin(Func):
4327    arg_types = {"this": True, "expression": True, "null": False}
4328
4329
4330class ArraySize(Func):
4331    arg_types = {"this": True, "expression": False}
4332
4333
4334class ArraySort(Func):
4335    arg_types = {"this": True, "expression": False}
4336
4337
4338class ArraySum(Func):
4339    pass
4340
4341
4342class ArrayUnionAgg(AggFunc):
4343    pass
4344
4345
4346class Avg(AggFunc):
4347    pass
4348
4349
4350class AnyValue(AggFunc):
4351    arg_types = {"this": True, "having": False, "max": False, "ignore_nulls": False}
4352
4353
4354class First(Func):
4355    arg_types = {"this": True, "ignore_nulls": False}
4356
4357
4358class Last(Func):
4359    arg_types = {"this": True, "ignore_nulls": False}
4360
4361
4362class Case(Func):
4363    arg_types = {"this": False, "ifs": True, "default": False}
4364
4365    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4366        instance = maybe_copy(self, copy)
4367        instance.append(
4368            "ifs",
4369            If(
4370                this=maybe_parse(condition, copy=copy, **opts),
4371                true=maybe_parse(then, copy=copy, **opts),
4372            ),
4373        )
4374        return instance
4375
4376    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4377        instance = maybe_copy(self, copy)
4378        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4379        return instance
4380
4381
4382class Cast(Func):
4383    arg_types = {"this": True, "to": True, "format": False, "safe": False}
4384
4385    @property
4386    def name(self) -> str:
4387        return self.this.name
4388
4389    @property
4390    def to(self) -> DataType:
4391        return self.args["to"]
4392
4393    @property
4394    def output_name(self) -> str:
4395        return self.name
4396
4397    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
4398        """
4399        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4400        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4401        array<int> != array<float>.
4402
4403        Args:
4404            dtypes: the data types to compare this Cast's DataType to.
4405
4406        Returns:
4407            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4408        """
4409        return self.to.is_type(*dtypes)
4410
4411
4412class TryCast(Cast):
4413    pass
4414
4415
4416class CastToStrType(Func):
4417    arg_types = {"this": True, "to": True}
4418
4419
4420class Collate(Binary, Func):
4421    pass
4422
4423
4424class Ceil(Func):
4425    arg_types = {"this": True, "decimals": False}
4426    _sql_names = ["CEIL", "CEILING"]
4427
4428
4429class Coalesce(Func):
4430    arg_types = {"this": True, "expressions": False}
4431    is_var_len_args = True
4432    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4433
4434
4435class Chr(Func):
4436    arg_types = {"this": True, "charset": False, "expressions": False}
4437    is_var_len_args = True
4438    _sql_names = ["CHR", "CHAR"]
4439
4440
4441class Concat(Func):
4442    arg_types = {"expressions": True}
4443    is_var_len_args = True
4444
4445
4446class SafeConcat(Concat):
4447    pass
4448
4449
4450class ConcatWs(Concat):
4451    _sql_names = ["CONCAT_WS"]
4452
4453
4454class Count(AggFunc):
4455    arg_types = {"this": False, "expressions": False}
4456    is_var_len_args = True
4457
4458
4459class CountIf(AggFunc):
4460    pass
4461
4462
4463class CurrentDate(Func):
4464    arg_types = {"this": False}
4465
4466
4467class CurrentDatetime(Func):
4468    arg_types = {"this": False}
4469
4470
4471class CurrentTime(Func):
4472    arg_types = {"this": False}
4473
4474
4475class CurrentTimestamp(Func):
4476    arg_types = {"this": False}
4477
4478
4479class CurrentUser(Func):
4480    arg_types = {"this": False}
4481
4482
4483class DateAdd(Func, IntervalOp):
4484    arg_types = {"this": True, "expression": True, "unit": False}
4485
4486
4487class DateSub(Func, IntervalOp):
4488    arg_types = {"this": True, "expression": True, "unit": False}
4489
4490
4491class DateDiff(Func, TimeUnit):
4492    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4493    arg_types = {"this": True, "expression": True, "unit": False}
4494
4495
4496class DateTrunc(Func):
4497    arg_types = {"unit": True, "this": True, "zone": False}
4498
4499    @property
4500    def unit(self) -> Expression:
4501        return self.args["unit"]
4502
4503
4504class DatetimeAdd(Func, IntervalOp):
4505    arg_types = {"this": True, "expression": True, "unit": False}
4506
4507
4508class DatetimeSub(Func, IntervalOp):
4509    arg_types = {"this": True, "expression": True, "unit": False}
4510
4511
4512class DatetimeDiff(Func, TimeUnit):
4513    arg_types = {"this": True, "expression": True, "unit": False}
4514
4515
4516class DatetimeTrunc(Func, TimeUnit):
4517    arg_types = {"this": True, "unit": True, "zone": False}
4518
4519
4520class DayOfWeek(Func):
4521    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4522
4523
4524class DayOfMonth(Func):
4525    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4526
4527
4528class DayOfYear(Func):
4529    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4530
4531
4532class ToDays(Func):
4533    pass
4534
4535
4536class WeekOfYear(Func):
4537    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4538
4539
4540class MonthsBetween(Func):
4541    arg_types = {"this": True, "expression": True, "roundoff": False}
4542
4543
4544class LastDateOfMonth(Func):
4545    pass
4546
4547
4548class Extract(Func):
4549    arg_types = {"this": True, "expression": True}
4550
4551
4552class Timestamp(Func):
4553    arg_types = {"this": False, "expression": False}
4554
4555
4556class TimestampAdd(Func, TimeUnit):
4557    arg_types = {"this": True, "expression": True, "unit": False}
4558
4559
4560class TimestampSub(Func, TimeUnit):
4561    arg_types = {"this": True, "expression": True, "unit": False}
4562
4563
4564class TimestampDiff(Func, TimeUnit):
4565    arg_types = {"this": True, "expression": True, "unit": False}
4566
4567
4568class TimestampTrunc(Func, TimeUnit):
4569    arg_types = {"this": True, "unit": True, "zone": False}
4570
4571
4572class TimeAdd(Func, TimeUnit):
4573    arg_types = {"this": True, "expression": True, "unit": False}
4574
4575
4576class TimeSub(Func, TimeUnit):
4577    arg_types = {"this": True, "expression": True, "unit": False}
4578
4579
4580class TimeDiff(Func, TimeUnit):
4581    arg_types = {"this": True, "expression": True, "unit": False}
4582
4583
4584class TimeTrunc(Func, TimeUnit):
4585    arg_types = {"this": True, "unit": True, "zone": False}
4586
4587
4588class DateFromParts(Func):
4589    _sql_names = ["DATEFROMPARTS"]
4590    arg_types = {"year": True, "month": True, "day": True}
4591
4592
4593class DateStrToDate(Func):
4594    pass
4595
4596
4597class DateToDateStr(Func):
4598    pass
4599
4600
4601class DateToDi(Func):
4602    pass
4603
4604
4605# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4606class Date(Func):
4607    arg_types = {"this": False, "zone": False, "expressions": False}
4608    is_var_len_args = True
4609
4610
4611class Day(Func):
4612    pass
4613
4614
4615class Decode(Func):
4616    arg_types = {"this": True, "charset": True, "replace": False}
4617
4618
4619class DiToDate(Func):
4620    pass
4621
4622
4623class Encode(Func):
4624    arg_types = {"this": True, "charset": True}
4625
4626
4627class Exp(Func):
4628    pass
4629
4630
4631# https://docs.snowflake.com/en/sql-reference/functions/flatten
4632class Explode(Func):
4633    arg_types = {"this": True, "expressions": False}
4634    is_var_len_args = True
4635
4636
4637class ExplodeOuter(Explode):
4638    pass
4639
4640
4641class Posexplode(Explode):
4642    pass
4643
4644
4645class PosexplodeOuter(Posexplode):
4646    pass
4647
4648
4649class Floor(Func):
4650    arg_types = {"this": True, "decimals": False}
4651
4652
4653class FromBase64(Func):
4654    pass
4655
4656
4657class ToBase64(Func):
4658    pass
4659
4660
4661class Greatest(Func):
4662    arg_types = {"this": True, "expressions": False}
4663    is_var_len_args = True
4664
4665
4666class GroupConcat(AggFunc):
4667    arg_types = {"this": True, "separator": False}
4668
4669
4670class Hex(Func):
4671    pass
4672
4673
4674class Xor(Connector, Func):
4675    arg_types = {"this": False, "expression": False, "expressions": False}
4676
4677
4678class If(Func):
4679    arg_types = {"this": True, "true": True, "false": False}
4680
4681
4682class Nullif(Func):
4683    arg_types = {"this": True, "expression": True}
4684
4685
4686class Initcap(Func):
4687    arg_types = {"this": True, "expression": False}
4688
4689
4690class IsNan(Func):
4691    _sql_names = ["IS_NAN", "ISNAN"]
4692
4693
4694class IsInf(Func):
4695    _sql_names = ["IS_INF", "ISINF"]
4696
4697
4698class FormatJson(Expression):
4699    pass
4700
4701
4702class JSONKeyValue(Expression):
4703    arg_types = {"this": True, "expression": True}
4704
4705
4706class JSONObject(Func):
4707    arg_types = {
4708        "expressions": False,
4709        "null_handling": False,
4710        "unique_keys": False,
4711        "return_type": False,
4712        "encoding": False,
4713    }
4714
4715
4716# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
4717class JSONArray(Func):
4718    arg_types = {
4719        "expressions": True,
4720        "null_handling": False,
4721        "return_type": False,
4722        "strict": False,
4723    }
4724
4725
4726# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
4727class JSONArrayAgg(Func):
4728    arg_types = {
4729        "this": True,
4730        "order": False,
4731        "null_handling": False,
4732        "return_type": False,
4733        "strict": False,
4734    }
4735
4736
4737# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4738# Note: parsing of JSON column definitions is currently incomplete.
4739class JSONColumnDef(Expression):
4740    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
4741
4742
4743class JSONSchema(Expression):
4744    arg_types = {"expressions": True}
4745
4746
4747# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4748class JSONTable(Func):
4749    arg_types = {
4750        "this": True,
4751        "schema": True,
4752        "path": False,
4753        "error_handling": False,
4754        "empty_handling": False,
4755    }
4756
4757
4758class OpenJSONColumnDef(Expression):
4759    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4760
4761
4762class OpenJSON(Func):
4763    arg_types = {"this": True, "path": False, "expressions": False}
4764
4765
4766class JSONBContains(Binary):
4767    _sql_names = ["JSONB_CONTAINS"]
4768
4769
4770class JSONExtract(Binary, Func):
4771    _sql_names = ["JSON_EXTRACT"]
4772
4773
4774class JSONExtractScalar(JSONExtract):
4775    _sql_names = ["JSON_EXTRACT_SCALAR"]
4776
4777
4778class JSONBExtract(JSONExtract):
4779    _sql_names = ["JSONB_EXTRACT"]
4780
4781
4782class JSONBExtractScalar(JSONExtract):
4783    _sql_names = ["JSONB_EXTRACT_SCALAR"]
4784
4785
4786class JSONFormat(Func):
4787    arg_types = {"this": False, "options": False}
4788    _sql_names = ["JSON_FORMAT"]
4789
4790
4791# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
4792class JSONArrayContains(Binary, Predicate, Func):
4793    _sql_names = ["JSON_ARRAY_CONTAINS"]
4794
4795
4796class ParseJSON(Func):
4797    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
4798    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
4799    arg_types = {"this": True, "expressions": False}
4800    is_var_len_args = True
4801
4802
4803class Least(Func):
4804    arg_types = {"this": True, "expressions": False}
4805    is_var_len_args = True
4806
4807
4808class Left(Func):
4809    arg_types = {"this": True, "expression": True}
4810
4811
4812class Right(Func):
4813    arg_types = {"this": True, "expression": True}
4814
4815
4816class Length(Func):
4817    _sql_names = ["LENGTH", "LEN"]
4818
4819
4820class Levenshtein(Func):
4821    arg_types = {
4822        "this": True,
4823        "expression": False,
4824        "ins_cost": False,
4825        "del_cost": False,
4826        "sub_cost": False,
4827    }
4828
4829
4830class Ln(Func):
4831    pass
4832
4833
4834class Log(Func):
4835    arg_types = {"this": True, "expression": False}
4836
4837
4838class Log2(Func):
4839    pass
4840
4841
4842class Log10(Func):
4843    pass
4844
4845
4846class LogicalOr(AggFunc):
4847    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
4848
4849
4850class LogicalAnd(AggFunc):
4851    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
4852
4853
4854class Lower(Func):
4855    _sql_names = ["LOWER", "LCASE"]
4856
4857
4858class Map(Func):
4859    arg_types = {"keys": False, "values": False}
4860
4861    @property
4862    def keys(self) -> t.List[Expression]:
4863        keys = self.args.get("keys")
4864        return keys.expressions if keys else []
4865
4866    @property
4867    def values(self) -> t.List[Expression]:
4868        values = self.args.get("values")
4869        return values.expressions if values else []
4870
4871
4872class MapFromEntries(Func):
4873    pass
4874
4875
4876class StarMap(Func):
4877    pass
4878
4879
4880class VarMap(Func):
4881    arg_types = {"keys": True, "values": True}
4882    is_var_len_args = True
4883
4884    @property
4885    def keys(self) -> t.List[Expression]:
4886        return self.args["keys"].expressions
4887
4888    @property
4889    def values(self) -> t.List[Expression]:
4890        return self.args["values"].expressions
4891
4892
4893# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
4894class MatchAgainst(Func):
4895    arg_types = {"this": True, "expressions": True, "modifier": False}
4896
4897
4898class Max(AggFunc):
4899    arg_types = {"this": True, "expressions": False}
4900    is_var_len_args = True
4901
4902
4903class MD5(Func):
4904    _sql_names = ["MD5"]
4905
4906
4907# Represents the variant of the MD5 function that returns a binary value
4908class MD5Digest(Func):
4909    _sql_names = ["MD5_DIGEST"]
4910
4911
4912class Min(AggFunc):
4913    arg_types = {"this": True, "expressions": False}
4914    is_var_len_args = True
4915
4916
4917class Month(Func):
4918    pass
4919
4920
4921class Nvl2(Func):
4922    arg_types = {"this": True, "true": True, "false": False}
4923
4924
4925# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
4926class Predict(Func):
4927    arg_types = {"this": True, "expression": True, "params_struct": False}
4928
4929
4930class Pow(Binary, Func):
4931    _sql_names = ["POWER", "POW"]
4932
4933
4934class PercentileCont(AggFunc):
4935    arg_types = {"this": True, "expression": False}
4936
4937
4938class PercentileDisc(AggFunc):
4939    arg_types = {"this": True, "expression": False}
4940
4941
4942class Quantile(AggFunc):
4943    arg_types = {"this": True, "quantile": True}
4944
4945
4946class ApproxQuantile(Quantile):
4947    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
4948
4949
4950class RangeN(Func):
4951    arg_types = {"this": True, "expressions": True, "each": False}
4952
4953
4954class ReadCSV(Func):
4955    _sql_names = ["READ_CSV"]
4956    is_var_len_args = True
4957    arg_types = {"this": True, "expressions": False}
4958
4959
4960class Reduce(Func):
4961    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
4962
4963
4964class RegexpExtract(Func):
4965    arg_types = {
4966        "this": True,
4967        "expression": True,
4968        "position": False,
4969        "occurrence": False,
4970        "parameters": False,
4971        "group": False,
4972    }
4973
4974
4975class RegexpReplace(Func):
4976    arg_types = {
4977        "this": True,
4978        "expression": True,
4979        "replacement": True,
4980        "position": False,
4981        "occurrence": False,
4982        "parameters": False,
4983        "modifiers": False,
4984    }
4985
4986
4987class RegexpLike(Binary, Func):
4988    arg_types = {"this": True, "expression": True, "flag": False}
4989
4990
4991class RegexpILike(Binary, Func):
4992    arg_types = {"this": True, "expression": True, "flag": False}
4993
4994
4995# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
4996# limit is the number of times a pattern is applied
4997class RegexpSplit(Func):
4998    arg_types = {"this": True, "expression": True, "limit": False}
4999
5000
5001class Repeat(Func):
5002    arg_types = {"this": True, "times": True}
5003
5004
5005class Round(Func):
5006    arg_types = {"this": True, "decimals": False}
5007
5008
5009class RowNumber(Func):
5010    arg_types: t.Dict[str, t.Any] = {}
5011
5012
5013class SafeDivide(Func):
5014    arg_types = {"this": True, "expression": True}
5015
5016
5017class SetAgg(AggFunc):
5018    pass
5019
5020
5021class SHA(Func):
5022    _sql_names = ["SHA", "SHA1"]
5023
5024
5025class SHA2(Func):
5026    _sql_names = ["SHA2"]
5027    arg_types = {"this": True, "length": False}
5028
5029
5030class SortArray(Func):
5031    arg_types = {"this": True, "asc": False}
5032
5033
5034class Split(Func):
5035    arg_types = {"this": True, "expression": True, "limit": False}
5036
5037
5038# Start may be omitted in the case of postgres
5039# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5040class Substring(Func):
5041    arg_types = {"this": True, "start": False, "length": False}
5042
5043
5044class StandardHash(Func):
5045    arg_types = {"this": True, "expression": False}
5046
5047
5048class StartsWith(Func):
5049    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5050    arg_types = {"this": True, "expression": True}
5051
5052
5053class StrPosition(Func):
5054    arg_types = {
5055        "this": True,
5056        "substr": True,
5057        "position": False,
5058        "instance": False,
5059    }
5060
5061
5062class StrToDate(Func):
5063    arg_types = {"this": True, "format": True}
5064
5065
5066class StrToTime(Func):
5067    arg_types = {"this": True, "format": True, "zone": False}
5068
5069
5070# Spark allows unix_timestamp()
5071# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5072class StrToUnix(Func):
5073    arg_types = {"this": False, "format": False}
5074
5075
5076# https://prestodb.io/docs/current/functions/string.html
5077# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5078class StrToMap(Func):
5079    arg_types = {
5080        "this": True,
5081        "pair_delim": False,
5082        "key_value_delim": False,
5083        "duplicate_resolution_callback": False,
5084    }
5085
5086
5087class NumberToStr(Func):
5088    arg_types = {"this": True, "format": True, "culture": False}
5089
5090
5091class FromBase(Func):
5092    arg_types = {"this": True, "expression": True}
5093
5094
5095class Struct(Func):
5096    arg_types = {"expressions": False}
5097    is_var_len_args = True
5098
5099
5100class StructExtract(Func):
5101    arg_types = {"this": True, "expression": True}
5102
5103
5104# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5105# https://docs.snowflake.com/en/sql-reference/functions/insert
5106class Stuff(Func):
5107    _sql_names = ["STUFF", "INSERT"]
5108    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5109
5110
5111class Sum(AggFunc):
5112    pass
5113
5114
5115class Sqrt(Func):
5116    pass
5117
5118
5119class Stddev(AggFunc):
5120    pass
5121
5122
5123class StddevPop(AggFunc):
5124    pass
5125
5126
5127class StddevSamp(AggFunc):
5128    pass
5129
5130
5131class TimeToStr(Func):
5132    arg_types = {"this": True, "format": True, "culture": False}
5133
5134
5135class TimeToTimeStr(Func):
5136    pass
5137
5138
5139class TimeToUnix(Func):
5140    pass
5141
5142
5143class TimeStrToDate(Func):
5144    pass
5145
5146
5147class TimeStrToTime(Func):
5148    pass
5149
5150
5151class TimeStrToUnix(Func):
5152    pass
5153
5154
5155class Trim(Func):
5156    arg_types = {
5157        "this": True,
5158        "expression": False,
5159        "position": False,
5160        "collation": False,
5161    }
5162
5163
5164class TsOrDsAdd(Func, TimeUnit):
5165    arg_types = {"this": True, "expression": True, "unit": False}
5166
5167
5168class TsOrDsToDateStr(Func):
5169    pass
5170
5171
5172class TsOrDsToDate(Func):
5173    arg_types = {"this": True, "format": False}
5174
5175
5176class TsOrDiToDi(Func):
5177    pass
5178
5179
5180class Unhex(Func):
5181    pass
5182
5183
5184class UnixToStr(Func):
5185    arg_types = {"this": True, "format": False}
5186
5187
5188# https://prestodb.io/docs/current/functions/datetime.html
5189# presto has weird zone/hours/minutes
5190class UnixToTime(Func):
5191    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5192
5193    SECONDS = Literal.string("seconds")
5194    MILLIS = Literal.string("millis")
5195    MICROS = Literal.string("micros")
5196
5197
5198class UnixToTimeStr(Func):
5199    pass
5200
5201
5202class Upper(Func):
5203    _sql_names = ["UPPER", "UCASE"]
5204
5205
5206class Variance(AggFunc):
5207    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5208
5209
5210class VariancePop(AggFunc):
5211    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5212
5213
5214class Week(Func):
5215    arg_types = {"this": True, "mode": False}
5216
5217
5218class XMLTable(Func):
5219    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5220
5221
5222class Year(Func):
5223    pass
5224
5225
5226class Use(Expression):
5227    arg_types = {"this": True, "kind": False}
5228
5229
5230class Merge(Expression):
5231    arg_types = {"this": True, "using": True, "on": True, "expressions": True, "with": False}
5232
5233
5234class When(Func):
5235    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5236
5237
5238# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5239# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5240class NextValueFor(Func):
5241    arg_types = {"this": True, "order": False}
5242
5243
5244def _norm_arg(arg):
5245    return arg.lower() if type(arg) is str else arg
5246
5247
5248ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5249
5250
5251# Helpers
5252@t.overload
5253def maybe_parse(
5254    sql_or_expression: ExpOrStr,
5255    *,
5256    into: t.Type[E],
5257    dialect: DialectType = None,
5258    prefix: t.Optional[str] = None,
5259    copy: bool = False,
5260    **opts,
5261) -> E:
5262    ...
5263
5264
5265@t.overload
5266def maybe_parse(
5267    sql_or_expression: str | E,
5268    *,
5269    into: t.Optional[IntoType] = None,
5270    dialect: DialectType = None,
5271    prefix: t.Optional[str] = None,
5272    copy: bool = False,
5273    **opts,
5274) -> E:
5275    ...
5276
5277
5278def maybe_parse(
5279    sql_or_expression: ExpOrStr,
5280    *,
5281    into: t.Optional[IntoType] = None,
5282    dialect: DialectType = None,
5283    prefix: t.Optional[str] = None,
5284    copy: bool = False,
5285    **opts,
5286) -> Expression:
5287    """Gracefully handle a possible string or expression.
5288
5289    Example:
5290        >>> maybe_parse("1")
5291        (LITERAL this: 1, is_string: False)
5292        >>> maybe_parse(to_identifier("x"))
5293        (IDENTIFIER this: x, quoted: False)
5294
5295    Args:
5296        sql_or_expression: the SQL code string or an expression
5297        into: the SQLGlot Expression to parse into
5298        dialect: the dialect used to parse the input expressions (in the case that an
5299            input expression is a SQL string).
5300        prefix: a string to prefix the sql with before it gets parsed
5301            (automatically includes a space)
5302        copy: whether or not to copy the expression.
5303        **opts: other options to use to parse the input expressions (again, in the case
5304            that an input expression is a SQL string).
5305
5306    Returns:
5307        Expression: the parsed or given expression.
5308    """
5309    if isinstance(sql_or_expression, Expression):
5310        if copy:
5311            return sql_or_expression.copy()
5312        return sql_or_expression
5313
5314    if sql_or_expression is None:
5315        raise ParseError(f"SQL cannot be None")
5316
5317    import sqlglot
5318
5319    sql = str(sql_or_expression)
5320    if prefix:
5321        sql = f"{prefix} {sql}"
5322
5323    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5324
5325
5326@t.overload
5327def maybe_copy(instance: None, copy: bool = True) -> None:
5328    ...
5329
5330
5331@t.overload
5332def maybe_copy(instance: E, copy: bool = True) -> E:
5333    ...
5334
5335
5336def maybe_copy(instance, copy=True):
5337    return instance.copy() if copy and instance else instance
5338
5339
5340def _is_wrong_expression(expression, into):
5341    return isinstance(expression, Expression) and not isinstance(expression, into)
5342
5343
5344def _apply_builder(
5345    expression,
5346    instance,
5347    arg,
5348    copy=True,
5349    prefix=None,
5350    into=None,
5351    dialect=None,
5352    into_arg="this",
5353    **opts,
5354):
5355    if _is_wrong_expression(expression, into):
5356        expression = into(**{into_arg: expression})
5357    instance = maybe_copy(instance, copy)
5358    expression = maybe_parse(
5359        sql_or_expression=expression,
5360        prefix=prefix,
5361        into=into,
5362        dialect=dialect,
5363        **opts,
5364    )
5365    instance.set(arg, expression)
5366    return instance
5367
5368
5369def _apply_child_list_builder(
5370    *expressions,
5371    instance,
5372    arg,
5373    append=True,
5374    copy=True,
5375    prefix=None,
5376    into=None,
5377    dialect=None,
5378    properties=None,
5379    **opts,
5380):
5381    instance = maybe_copy(instance, copy)
5382    parsed = []
5383    for expression in expressions:
5384        if expression is not None:
5385            if _is_wrong_expression(expression, into):
5386                expression = into(expressions=[expression])
5387
5388            expression = maybe_parse(
5389                expression,
5390                into=into,
5391                dialect=dialect,
5392                prefix=prefix,
5393                **opts,
5394            )
5395            parsed.extend(expression.expressions)
5396
5397    existing = instance.args.get(arg)
5398    if append and existing:
5399        parsed = existing.expressions + parsed
5400
5401    child = into(expressions=parsed)
5402    for k, v in (properties or {}).items():
5403        child.set(k, v)
5404    instance.set(arg, child)
5405
5406    return instance
5407
5408
5409def _apply_list_builder(
5410    *expressions,
5411    instance,
5412    arg,
5413    append=True,
5414    copy=True,
5415    prefix=None,
5416    into=None,
5417    dialect=None,
5418    **opts,
5419):
5420    inst = maybe_copy(instance, copy)
5421
5422    expressions = [
5423        maybe_parse(
5424            sql_or_expression=expression,
5425            into=into,
5426            prefix=prefix,
5427            dialect=dialect,
5428            **opts,
5429        )
5430        for expression in expressions
5431        if expression is not None
5432    ]
5433
5434    existing_expressions = inst.args.get(arg)
5435    if append and existing_expressions:
5436        expressions = existing_expressions + expressions
5437
5438    inst.set(arg, expressions)
5439    return inst
5440
5441
5442def _apply_conjunction_builder(
5443    *expressions,
5444    instance,
5445    arg,
5446    into=None,
5447    append=True,
5448    copy=True,
5449    dialect=None,
5450    **opts,
5451):
5452    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5453    if not expressions:
5454        return instance
5455
5456    inst = maybe_copy(instance, copy)
5457
5458    existing = inst.args.get(arg)
5459    if append and existing is not None:
5460        expressions = [existing.this if into else existing] + list(expressions)
5461
5462    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5463
5464    inst.set(arg, into(this=node) if into else node)
5465    return inst
5466
5467
5468def _apply_cte_builder(
5469    instance: E,
5470    alias: ExpOrStr,
5471    as_: ExpOrStr,
5472    recursive: t.Optional[bool] = None,
5473    append: bool = True,
5474    dialect: DialectType = None,
5475    copy: bool = True,
5476    **opts,
5477) -> E:
5478    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5479    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5480    cte = CTE(this=as_expression, alias=alias_expression)
5481    return _apply_child_list_builder(
5482        cte,
5483        instance=instance,
5484        arg="with",
5485        append=append,
5486        copy=copy,
5487        into=With,
5488        properties={"recursive": recursive or False},
5489    )
5490
5491
5492def _combine(
5493    expressions: t.Sequence[t.Optional[ExpOrStr]],
5494    operator: t.Type[Connector],
5495    dialect: DialectType = None,
5496    copy: bool = True,
5497    **opts,
5498) -> Expression:
5499    conditions = [
5500        condition(expression, dialect=dialect, copy=copy, **opts)
5501        for expression in expressions
5502        if expression is not None
5503    ]
5504
5505    this, *rest = conditions
5506    if rest:
5507        this = _wrap(this, Connector)
5508    for expression in rest:
5509        this = operator(this=this, expression=_wrap(expression, Connector))
5510
5511    return this
5512
5513
5514def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5515    return Paren(this=expression) if isinstance(expression, kind) else expression
5516
5517
5518def union(
5519    left: ExpOrStr,
5520    right: ExpOrStr,
5521    distinct: bool = True,
5522    dialect: DialectType = None,
5523    copy: bool = True,
5524    **opts,
5525) -> Union:
5526    """
5527    Initializes a syntax tree from one UNION expression.
5528
5529    Example:
5530        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5531        'SELECT * FROM foo UNION SELECT * FROM bla'
5532
5533    Args:
5534        left: the SQL code string corresponding to the left-hand side.
5535            If an `Expression` instance is passed, it will be used as-is.
5536        right: the SQL code string corresponding to the right-hand side.
5537            If an `Expression` instance is passed, it will be used as-is.
5538        distinct: set the DISTINCT flag if and only if this is true.
5539        dialect: the dialect used to parse the input expression.
5540        copy: whether or not to copy the expression.
5541        opts: other options to use to parse the input expressions.
5542
5543    Returns:
5544        The new Union instance.
5545    """
5546    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5547    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5548
5549    return Union(this=left, expression=right, distinct=distinct)
5550
5551
5552def intersect(
5553    left: ExpOrStr,
5554    right: ExpOrStr,
5555    distinct: bool = True,
5556    dialect: DialectType = None,
5557    copy: bool = True,
5558    **opts,
5559) -> Intersect:
5560    """
5561    Initializes a syntax tree from one INTERSECT expression.
5562
5563    Example:
5564        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5565        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5566
5567    Args:
5568        left: the SQL code string corresponding to the left-hand side.
5569            If an `Expression` instance is passed, it will be used as-is.
5570        right: the SQL code string corresponding to the right-hand side.
5571            If an `Expression` instance is passed, it will be used as-is.
5572        distinct: set the DISTINCT flag if and only if this is true.
5573        dialect: the dialect used to parse the input expression.
5574        copy: whether or not to copy the expression.
5575        opts: other options to use to parse the input expressions.
5576
5577    Returns:
5578        The new Intersect instance.
5579    """
5580    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5581    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5582
5583    return Intersect(this=left, expression=right, distinct=distinct)
5584
5585
5586def except_(
5587    left: ExpOrStr,
5588    right: ExpOrStr,
5589    distinct: bool = True,
5590    dialect: DialectType = None,
5591    copy: bool = True,
5592    **opts,
5593) -> Except:
5594    """
5595    Initializes a syntax tree from one EXCEPT expression.
5596
5597    Example:
5598        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5599        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5600
5601    Args:
5602        left: the SQL code string corresponding to the left-hand side.
5603            If an `Expression` instance is passed, it will be used as-is.
5604        right: the SQL code string corresponding to the right-hand side.
5605            If an `Expression` instance is passed, it will be used as-is.
5606        distinct: set the DISTINCT flag if and only if this is true.
5607        dialect: the dialect used to parse the input expression.
5608        copy: whether or not to copy the expression.
5609        opts: other options to use to parse the input expressions.
5610
5611    Returns:
5612        The new Except instance.
5613    """
5614    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5615    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5616
5617    return Except(this=left, expression=right, distinct=distinct)
5618
5619
5620def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5621    """
5622    Initializes a syntax tree from one or multiple SELECT expressions.
5623
5624    Example:
5625        >>> select("col1", "col2").from_("tbl").sql()
5626        'SELECT col1, col2 FROM tbl'
5627
5628    Args:
5629        *expressions: the SQL code string to parse as the expressions of a
5630            SELECT statement. If an Expression instance is passed, this is used as-is.
5631        dialect: the dialect used to parse the input expressions (in the case that an
5632            input expression is a SQL string).
5633        **opts: other options to use to parse the input expressions (again, in the case
5634            that an input expression is a SQL string).
5635
5636    Returns:
5637        Select: the syntax tree for the SELECT statement.
5638    """
5639    return Select().select(*expressions, dialect=dialect, **opts)
5640
5641
5642def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5643    """
5644    Initializes a syntax tree from a FROM expression.
5645
5646    Example:
5647        >>> from_("tbl").select("col1", "col2").sql()
5648        'SELECT col1, col2 FROM tbl'
5649
5650    Args:
5651        *expression: the SQL code string to parse as the FROM expressions of a
5652            SELECT statement. If an Expression instance is passed, this is used as-is.
5653        dialect: the dialect used to parse the input expression (in the case that the
5654            input expression is a SQL string).
5655        **opts: other options to use to parse the input expressions (again, in the case
5656            that the input expression is a SQL string).
5657
5658    Returns:
5659        Select: the syntax tree for the SELECT statement.
5660    """
5661    return Select().from_(expression, dialect=dialect, **opts)
5662
5663
5664def update(
5665    table: str | Table,
5666    properties: dict,
5667    where: t.Optional[ExpOrStr] = None,
5668    from_: t.Optional[ExpOrStr] = None,
5669    dialect: DialectType = None,
5670    **opts,
5671) -> Update:
5672    """
5673    Creates an update statement.
5674
5675    Example:
5676        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
5677        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
5678
5679    Args:
5680        *properties: dictionary of properties to set which are
5681            auto converted to sql objects eg None -> NULL
5682        where: sql conditional parsed into a WHERE statement
5683        from_: sql statement parsed into a FROM statement
5684        dialect: the dialect used to parse the input expressions.
5685        **opts: other options to use to parse the input expressions.
5686
5687    Returns:
5688        Update: the syntax tree for the UPDATE statement.
5689    """
5690    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
5691    update_expr.set(
5692        "expressions",
5693        [
5694            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
5695            for k, v in properties.items()
5696        ],
5697    )
5698    if from_:
5699        update_expr.set(
5700            "from",
5701            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
5702        )
5703    if isinstance(where, Condition):
5704        where = Where(this=where)
5705    if where:
5706        update_expr.set(
5707            "where",
5708            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
5709        )
5710    return update_expr
5711
5712
5713def delete(
5714    table: ExpOrStr,
5715    where: t.Optional[ExpOrStr] = None,
5716    returning: t.Optional[ExpOrStr] = None,
5717    dialect: DialectType = None,
5718    **opts,
5719) -> Delete:
5720    """
5721    Builds a delete statement.
5722
5723    Example:
5724        >>> delete("my_table", where="id > 1").sql()
5725        'DELETE FROM my_table WHERE id > 1'
5726
5727    Args:
5728        where: sql conditional parsed into a WHERE statement
5729        returning: sql conditional parsed into a RETURNING statement
5730        dialect: the dialect used to parse the input expressions.
5731        **opts: other options to use to parse the input expressions.
5732
5733    Returns:
5734        Delete: the syntax tree for the DELETE statement.
5735    """
5736    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
5737    if where:
5738        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
5739    if returning:
5740        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
5741    return delete_expr
5742
5743
5744def insert(
5745    expression: ExpOrStr,
5746    into: ExpOrStr,
5747    columns: t.Optional[t.Sequence[ExpOrStr]] = None,
5748    overwrite: t.Optional[bool] = None,
5749    dialect: DialectType = None,
5750    copy: bool = True,
5751    **opts,
5752) -> Insert:
5753    """
5754    Builds an INSERT statement.
5755
5756    Example:
5757        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
5758        'INSERT INTO tbl VALUES (1, 2, 3)'
5759
5760    Args:
5761        expression: the sql string or expression of the INSERT statement
5762        into: the tbl to insert data to.
5763        columns: optionally the table's column names.
5764        overwrite: whether to INSERT OVERWRITE or not.
5765        dialect: the dialect used to parse the input expressions.
5766        copy: whether or not to copy the expression.
5767        **opts: other options to use to parse the input expressions.
5768
5769    Returns:
5770        Insert: the syntax tree for the INSERT statement.
5771    """
5772    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5773    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
5774
5775    if columns:
5776        this = _apply_list_builder(
5777            *columns,
5778            instance=Schema(this=this),
5779            arg="expressions",
5780            into=Identifier,
5781            copy=False,
5782            dialect=dialect,
5783            **opts,
5784        )
5785
5786    return Insert(this=this, expression=expr, overwrite=overwrite)
5787
5788
5789def condition(
5790    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
5791) -> Condition:
5792    """
5793    Initialize a logical condition expression.
5794
5795    Example:
5796        >>> condition("x=1").sql()
5797        'x = 1'
5798
5799        This is helpful for composing larger logical syntax trees:
5800        >>> where = condition("x=1")
5801        >>> where = where.and_("y=1")
5802        >>> Select().from_("tbl").select("*").where(where).sql()
5803        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
5804
5805    Args:
5806        *expression: the SQL code string to parse.
5807            If an Expression instance is passed, this is used as-is.
5808        dialect: the dialect used to parse the input expression (in the case that the
5809            input expression is a SQL string).
5810        copy: Whether or not to copy `expression` (only applies to expressions).
5811        **opts: other options to use to parse the input expressions (again, in the case
5812            that the input expression is a SQL string).
5813
5814    Returns:
5815        The new Condition instance
5816    """
5817    return maybe_parse(
5818        expression,
5819        into=Condition,
5820        dialect=dialect,
5821        copy=copy,
5822        **opts,
5823    )
5824
5825
5826def and_(
5827    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5828) -> Condition:
5829    """
5830    Combine multiple conditions with an AND logical operator.
5831
5832    Example:
5833        >>> and_("x=1", and_("y=1", "z=1")).sql()
5834        'x = 1 AND (y = 1 AND z = 1)'
5835
5836    Args:
5837        *expressions: the SQL code strings to parse.
5838            If an Expression instance is passed, this is used as-is.
5839        dialect: the dialect used to parse the input expression.
5840        copy: whether or not to copy `expressions` (only applies to Expressions).
5841        **opts: other options to use to parse the input expressions.
5842
5843    Returns:
5844        And: the new condition
5845    """
5846    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
5847
5848
5849def or_(
5850    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5851) -> Condition:
5852    """
5853    Combine multiple conditions with an OR logical operator.
5854
5855    Example:
5856        >>> or_("x=1", or_("y=1", "z=1")).sql()
5857        'x = 1 OR (y = 1 OR z = 1)'
5858
5859    Args:
5860        *expressions: the SQL code strings to parse.
5861            If an Expression instance is passed, this is used as-is.
5862        dialect: the dialect used to parse the input expression.
5863        copy: whether or not to copy `expressions` (only applies to Expressions).
5864        **opts: other options to use to parse the input expressions.
5865
5866    Returns:
5867        Or: the new condition
5868    """
5869    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
5870
5871
5872def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
5873    """
5874    Wrap a condition with a NOT operator.
5875
5876    Example:
5877        >>> not_("this_suit='black'").sql()
5878        "NOT this_suit = 'black'"
5879
5880    Args:
5881        expression: the SQL code string to parse.
5882            If an Expression instance is passed, this is used as-is.
5883        dialect: the dialect used to parse the input expression.
5884        copy: whether to copy the expression or not.
5885        **opts: other options to use to parse the input expressions.
5886
5887    Returns:
5888        The new condition.
5889    """
5890    this = condition(
5891        expression,
5892        dialect=dialect,
5893        copy=copy,
5894        **opts,
5895    )
5896    return Not(this=_wrap(this, Connector))
5897
5898
5899def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
5900    """
5901    Wrap an expression in parentheses.
5902
5903    Example:
5904        >>> paren("5 + 3").sql()
5905        '(5 + 3)'
5906
5907    Args:
5908        expression: the SQL code string to parse.
5909            If an Expression instance is passed, this is used as-is.
5910        copy: whether to copy the expression or not.
5911
5912    Returns:
5913        The wrapped expression.
5914    """
5915    return Paren(this=maybe_parse(expression, copy=copy))
5916
5917
5918SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
5919
5920
5921@t.overload
5922def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
5923    ...
5924
5925
5926@t.overload
5927def to_identifier(
5928    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
5929) -> Identifier:
5930    ...
5931
5932
5933def to_identifier(name, quoted=None, copy=True):
5934    """Builds an identifier.
5935
5936    Args:
5937        name: The name to turn into an identifier.
5938        quoted: Whether or not force quote the identifier.
5939        copy: Whether or not to copy name if it's an Identifier.
5940
5941    Returns:
5942        The identifier ast node.
5943    """
5944
5945    if name is None:
5946        return None
5947
5948    if isinstance(name, Identifier):
5949        identifier = maybe_copy(name, copy)
5950    elif isinstance(name, str):
5951        identifier = Identifier(
5952            this=name,
5953            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
5954        )
5955    else:
5956        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
5957    return identifier
5958
5959
5960def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
5961    """
5962    Parses a given string into an identifier.
5963
5964    Args:
5965        name: The name to parse into an identifier.
5966        dialect: The dialect to parse against.
5967
5968    Returns:
5969        The identifier ast node.
5970    """
5971    try:
5972        expression = maybe_parse(name, dialect=dialect, into=Identifier)
5973    except ParseError:
5974        expression = to_identifier(name)
5975
5976    return expression
5977
5978
5979INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
5980
5981
5982def to_interval(interval: str | Literal) -> Interval:
5983    """Builds an interval expression from a string like '1 day' or '5 months'."""
5984    if isinstance(interval, Literal):
5985        if not interval.is_string:
5986            raise ValueError("Invalid interval string.")
5987
5988        interval = interval.this
5989
5990    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
5991
5992    if not interval_parts:
5993        raise ValueError("Invalid interval string.")
5994
5995    return Interval(
5996        this=Literal.string(interval_parts.group(1)),
5997        unit=Var(this=interval_parts.group(2)),
5998    )
5999
6000
6001@t.overload
6002def to_table(sql_path: str | Table, **kwargs) -> Table:
6003    ...
6004
6005
6006@t.overload
6007def to_table(sql_path: None, **kwargs) -> None:
6008    ...
6009
6010
6011def to_table(
6012    sql_path: t.Optional[str | Table], dialect: DialectType = None, **kwargs
6013) -> t.Optional[Table]:
6014    """
6015    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6016    If a table is passed in then that table is returned.
6017
6018    Args:
6019        sql_path: a `[catalog].[schema].[table]` string.
6020        dialect: the source dialect according to which the table name will be parsed.
6021        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6022
6023    Returns:
6024        A table expression.
6025    """
6026    if sql_path is None or isinstance(sql_path, Table):
6027        return sql_path
6028    if not isinstance(sql_path, str):
6029        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
6030
6031    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6032    if table:
6033        for k, v in kwargs.items():
6034            table.set(k, v)
6035
6036    return table
6037
6038
6039def to_column(sql_path: str | Column, **kwargs) -> Column:
6040    """
6041    Create a column from a `[table].[column]` sql path. Schema is optional.
6042
6043    If a column is passed in then that column is returned.
6044
6045    Args:
6046        sql_path: `[table].[column]` string
6047    Returns:
6048        Table: A column expression
6049    """
6050    if sql_path is None or isinstance(sql_path, Column):
6051        return sql_path
6052    if not isinstance(sql_path, str):
6053        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6054    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6055
6056
6057def alias_(
6058    expression: ExpOrStr,
6059    alias: str | Identifier,
6060    table: bool | t.Sequence[str | Identifier] = False,
6061    quoted: t.Optional[bool] = None,
6062    dialect: DialectType = None,
6063    copy: bool = True,
6064    **opts,
6065):
6066    """Create an Alias expression.
6067
6068    Example:
6069        >>> alias_('foo', 'bar').sql()
6070        'foo AS bar'
6071
6072        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6073        '(SELECT 1, 2) AS bar(a, b)'
6074
6075    Args:
6076        expression: the SQL code strings to parse.
6077            If an Expression instance is passed, this is used as-is.
6078        alias: the alias name to use. If the name has
6079            special characters it is quoted.
6080        table: Whether or not to create a table alias, can also be a list of columns.
6081        quoted: whether or not to quote the alias
6082        dialect: the dialect used to parse the input expression.
6083        copy: Whether or not to copy the expression.
6084        **opts: other options to use to parse the input expressions.
6085
6086    Returns:
6087        Alias: the aliased expression
6088    """
6089    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6090    alias = to_identifier(alias, quoted=quoted)
6091
6092    if table:
6093        table_alias = TableAlias(this=alias)
6094        exp.set("alias", table_alias)
6095
6096        if not isinstance(table, bool):
6097            for column in table:
6098                table_alias.append("columns", to_identifier(column, quoted=quoted))
6099
6100        return exp
6101
6102    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6103    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6104    # for the complete Window expression.
6105    #
6106    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6107
6108    if "alias" in exp.arg_types and not isinstance(exp, Window):
6109        exp.set("alias", alias)
6110        return exp
6111    return Alias(this=exp, alias=alias)
6112
6113
6114def subquery(
6115    expression: ExpOrStr,
6116    alias: t.Optional[Identifier | str] = None,
6117    dialect: DialectType = None,
6118    **opts,
6119) -> Select:
6120    """
6121    Build a subquery expression.
6122
6123    Example:
6124        >>> subquery('select x from tbl', 'bar').select('x').sql()
6125        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6126
6127    Args:
6128        expression: the SQL code strings to parse.
6129            If an Expression instance is passed, this is used as-is.
6130        alias: the alias name to use.
6131        dialect: the dialect used to parse the input expression.
6132        **opts: other options to use to parse the input expressions.
6133
6134    Returns:
6135        A new Select instance with the subquery expression included.
6136    """
6137
6138    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6139    return Select().from_(expression, dialect=dialect, **opts)
6140
6141
6142def column(
6143    col: str | Identifier,
6144    table: t.Optional[str | Identifier] = None,
6145    db: t.Optional[str | Identifier] = None,
6146    catalog: t.Optional[str | Identifier] = None,
6147    quoted: t.Optional[bool] = None,
6148) -> Column:
6149    """
6150    Build a Column.
6151
6152    Args:
6153        col: Column name.
6154        table: Table name.
6155        db: Database name.
6156        catalog: Catalog name.
6157        quoted: Whether to force quotes on the column's identifiers.
6158
6159    Returns:
6160        The new Column instance.
6161    """
6162    return Column(
6163        this=to_identifier(col, quoted=quoted),
6164        table=to_identifier(table, quoted=quoted),
6165        db=to_identifier(db, quoted=quoted),
6166        catalog=to_identifier(catalog, quoted=quoted),
6167    )
6168
6169
6170def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
6171    """Cast an expression to a data type.
6172
6173    Example:
6174        >>> cast('x + 1', 'int').sql()
6175        'CAST(x + 1 AS INT)'
6176
6177    Args:
6178        expression: The expression to cast.
6179        to: The datatype to cast to.
6180
6181    Returns:
6182        The new Cast instance.
6183    """
6184    expression = maybe_parse(expression, **opts)
6185    data_type = DataType.build(to, **opts)
6186    expression = Cast(this=expression, to=data_type)
6187    expression.type = data_type
6188    return expression
6189
6190
6191def table_(
6192    table: Identifier | str,
6193    db: t.Optional[Identifier | str] = None,
6194    catalog: t.Optional[Identifier | str] = None,
6195    quoted: t.Optional[bool] = None,
6196    alias: t.Optional[Identifier | str] = None,
6197) -> Table:
6198    """Build a Table.
6199
6200    Args:
6201        table: Table name.
6202        db: Database name.
6203        catalog: Catalog name.
6204        quote: Whether to force quotes on the table's identifiers.
6205        alias: Table's alias.
6206
6207    Returns:
6208        The new Table instance.
6209    """
6210    return Table(
6211        this=to_identifier(table, quoted=quoted) if table else None,
6212        db=to_identifier(db, quoted=quoted) if db else None,
6213        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6214        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6215    )
6216
6217
6218def values(
6219    values: t.Iterable[t.Tuple[t.Any, ...]],
6220    alias: t.Optional[str] = None,
6221    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6222) -> Values:
6223    """Build VALUES statement.
6224
6225    Example:
6226        >>> values([(1, '2')]).sql()
6227        "VALUES (1, '2')"
6228
6229    Args:
6230        values: values statements that will be converted to SQL
6231        alias: optional alias
6232        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6233         If either are provided then an alias is also required.
6234
6235    Returns:
6236        Values: the Values expression object
6237    """
6238    if columns and not alias:
6239        raise ValueError("Alias is required when providing columns")
6240
6241    return Values(
6242        expressions=[convert(tup) for tup in values],
6243        alias=(
6244            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6245            if columns
6246            else (TableAlias(this=to_identifier(alias)) if alias else None)
6247        ),
6248    )
6249
6250
6251def var(name: t.Optional[ExpOrStr]) -> Var:
6252    """Build a SQL variable.
6253
6254    Example:
6255        >>> repr(var('x'))
6256        '(VAR this: x)'
6257
6258        >>> repr(var(column('x', table='y')))
6259        '(VAR this: x)'
6260
6261    Args:
6262        name: The name of the var or an expression who's name will become the var.
6263
6264    Returns:
6265        The new variable node.
6266    """
6267    if not name:
6268        raise ValueError("Cannot convert empty name into var.")
6269
6270    if isinstance(name, Expression):
6271        name = name.name
6272    return Var(this=name)
6273
6274
6275def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6276    """Build ALTER TABLE... RENAME... expression
6277
6278    Args:
6279        old_name: The old name of the table
6280        new_name: The new name of the table
6281
6282    Returns:
6283        Alter table expression
6284    """
6285    old_table = to_table(old_name)
6286    new_table = to_table(new_name)
6287    return AlterTable(
6288        this=old_table,
6289        actions=[
6290            RenameTable(this=new_table),
6291        ],
6292    )
6293
6294
6295def convert(value: t.Any, copy: bool = False) -> Expression:
6296    """Convert a python value into an expression object.
6297
6298    Raises an error if a conversion is not possible.
6299
6300    Args:
6301        value: A python object.
6302        copy: Whether or not to copy `value` (only applies to Expressions and collections).
6303
6304    Returns:
6305        Expression: the equivalent expression object.
6306    """
6307    if isinstance(value, Expression):
6308        return maybe_copy(value, copy)
6309    if isinstance(value, str):
6310        return Literal.string(value)
6311    if isinstance(value, bool):
6312        return Boolean(this=value)
6313    if value is None or (isinstance(value, float) and math.isnan(value)):
6314        return NULL
6315    if isinstance(value, numbers.Number):
6316        return Literal.number(value)
6317    if isinstance(value, datetime.datetime):
6318        datetime_literal = Literal.string(
6319            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6320        )
6321        return TimeStrToTime(this=datetime_literal)
6322    if isinstance(value, datetime.date):
6323        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6324        return DateStrToDate(this=date_literal)
6325    if isinstance(value, tuple):
6326        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6327    if isinstance(value, list):
6328        return Array(expressions=[convert(v, copy=copy) for v in value])
6329    if isinstance(value, dict):
6330        return Map(
6331            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6332            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6333        )
6334    raise ValueError(f"Cannot convert {value}")
6335
6336
6337def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6338    """
6339    Replace children of an expression with the result of a lambda fun(child) -> exp.
6340    """
6341    for k, v in expression.args.items():
6342        is_list_arg = type(v) is list
6343
6344        child_nodes = v if is_list_arg else [v]
6345        new_child_nodes = []
6346
6347        for cn in child_nodes:
6348            if isinstance(cn, Expression):
6349                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6350                    new_child_nodes.append(child_node)
6351                    child_node.parent = expression
6352                    child_node.arg_key = k
6353            else:
6354                new_child_nodes.append(cn)
6355
6356        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6357
6358
6359def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6360    """
6361    Return all table names referenced through columns in an expression.
6362
6363    Example:
6364        >>> import sqlglot
6365        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6366        ['a', 'c']
6367
6368    Args:
6369        expression: expression to find table names.
6370        exclude: a table name to exclude
6371
6372    Returns:
6373        A list of unique names.
6374    """
6375    return {
6376        table
6377        for table in (column.table for column in expression.find_all(Column))
6378        if table and table != exclude
6379    }
6380
6381
6382def table_name(table: Table | str, dialect: DialectType = None) -> str:
6383    """Get the full name of a table as a string.
6384
6385    Args:
6386        table: Table expression node or string.
6387        dialect: The dialect to generate the table name for.
6388
6389    Examples:
6390        >>> from sqlglot import exp, parse_one
6391        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6392        'a.b.c'
6393
6394    Returns:
6395        The table name.
6396    """
6397
6398    table = maybe_parse(table, into=Table, dialect=dialect)
6399
6400    if not table:
6401        raise ValueError(f"Cannot parse {table}")
6402
6403    return ".".join(
6404        part.sql(dialect=dialect, identify=True)
6405        if not SAFE_IDENTIFIER_RE.match(part.name)
6406        else part.name
6407        for part in table.parts
6408    )
6409
6410
6411def replace_tables(expression: E, mapping: t.Dict[str, str], copy: bool = True) -> E:
6412    """Replace all tables in expression according to the mapping.
6413
6414    Args:
6415        expression: expression node to be transformed and replaced.
6416        mapping: mapping of table names.
6417        copy: whether or not to copy the expression.
6418
6419    Examples:
6420        >>> from sqlglot import exp, parse_one
6421        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6422        'SELECT * FROM c'
6423
6424    Returns:
6425        The mapped expression.
6426    """
6427
6428    def _replace_tables(node: Expression) -> Expression:
6429        if isinstance(node, Table):
6430            new_name = mapping.get(table_name(node))
6431            if new_name:
6432                return to_table(
6433                    new_name,
6434                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
6435                )
6436        return node
6437
6438    return expression.transform(_replace_tables, copy=copy)
6439
6440
6441def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6442    """Replace placeholders in an expression.
6443
6444    Args:
6445        expression: expression node to be transformed and replaced.
6446        args: positional names that will substitute unnamed placeholders in the given order.
6447        kwargs: keyword arguments that will substitute named placeholders.
6448
6449    Examples:
6450        >>> from sqlglot import exp, parse_one
6451        >>> replace_placeholders(
6452        ...     parse_one("select * from :tbl where ? = ?"),
6453        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6454        ... ).sql()
6455        "SELECT * FROM foo WHERE str_col = 'b'"
6456
6457    Returns:
6458        The mapped expression.
6459    """
6460
6461    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6462        if isinstance(node, Placeholder):
6463            if node.name:
6464                new_name = kwargs.get(node.name)
6465                if new_name:
6466                    return convert(new_name)
6467            else:
6468                try:
6469                    return convert(next(args))
6470                except StopIteration:
6471                    pass
6472        return node
6473
6474    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6475
6476
6477def expand(
6478    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
6479) -> Expression:
6480    """Transforms an expression by expanding all referenced sources into subqueries.
6481
6482    Examples:
6483        >>> from sqlglot import parse_one
6484        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6485        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6486
6487        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6488        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6489
6490    Args:
6491        expression: The expression to expand.
6492        sources: A dictionary of name to Subqueryables.
6493        copy: Whether or not to copy the expression during transformation. Defaults to True.
6494
6495    Returns:
6496        The transformed expression.
6497    """
6498
6499    def _expand(node: Expression):
6500        if isinstance(node, Table):
6501            name = table_name(node)
6502            source = sources.get(name)
6503            if source:
6504                subquery = source.subquery(node.alias or name)
6505                subquery.comments = [f"source: {name}"]
6506                return subquery.transform(_expand, copy=False)
6507        return node
6508
6509    return expression.transform(_expand, copy=copy)
6510
6511
6512def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
6513    """
6514    Returns a Func expression.
6515
6516    Examples:
6517        >>> func("abs", 5).sql()
6518        'ABS(5)'
6519
6520        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6521        'CAST(5 AS DOUBLE)'
6522
6523    Args:
6524        name: the name of the function to build.
6525        args: the args used to instantiate the function of interest.
6526        dialect: the source dialect.
6527        kwargs: the kwargs used to instantiate the function of interest.
6528
6529    Note:
6530        The arguments `args` and `kwargs` are mutually exclusive.
6531
6532    Returns:
6533        An instance of the function of interest, or an anonymous function, if `name` doesn't
6534        correspond to an existing `sqlglot.expressions.Func` class.
6535    """
6536    if args and kwargs:
6537        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6538
6539    from sqlglot.dialects.dialect import Dialect
6540
6541    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect) for arg in args]
6542    kwargs = {key: maybe_parse(value, dialect=dialect) for key, value in kwargs.items()}
6543
6544    parser = Dialect.get_or_raise(dialect)().parser()
6545    from_args_list = parser.FUNCTIONS.get(name.upper())
6546
6547    if from_args_list:
6548        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
6549    else:
6550        kwargs = kwargs or {"expressions": converted}
6551        function = Anonymous(this=name, **kwargs)
6552
6553    for error_message in function.error_messages(converted):
6554        raise ValueError(error_message)
6555
6556    return function
6557
6558
6559def case(
6560    expression: t.Optional[ExpOrStr] = None,
6561    **opts,
6562) -> Case:
6563    """
6564    Initialize a CASE statement.
6565
6566    Example:
6567        case().when("a = 1", "foo").else_("bar")
6568
6569    Args:
6570        expression: Optionally, the input expression (not all dialects support this)
6571        **opts: Extra keyword arguments for parsing `expression`
6572    """
6573    if expression is not None:
6574        this = maybe_parse(expression, **opts)
6575    else:
6576        this = None
6577    return Case(this=this, ifs=[])
6578
6579
6580def true() -> Boolean:
6581    """
6582    Returns a true Boolean expression.
6583    """
6584    return Boolean(this=True)
6585
6586
6587def false() -> Boolean:
6588    """
6589    Returns a false Boolean expression.
6590    """
6591    return Boolean(this=False)
6592
6593
6594def null() -> Null:
6595    """
6596    Returns a Null expression.
6597    """
6598    return Null()
6599
6600
6601# TODO: deprecate this
6602TRUE = Boolean(this=True)
6603FALSE = Boolean(this=False)
6604NULL = Null()